Say I have cells A1:A20. If the cell has a 1 in it, it means a "yes" response. If it has a 0, it's a "no" response.

In A21 I want to tally the number of yes responses. I'm assuming I would need an IF statement? I tried =IF(A1:A20>0,SUM(A1:A20)). I wasn't expecting that to actually work and it didn't I know why it doesn't work but I'm not sure how to write it to only take into account the cells with 1. HALP

8/30/2009 7:57:53 AM

use the COUNTIF function.

=COUNTIF(A1:A20,1)

8/30/2009 8:27:14 AM

=SUMIF(A1:A20,"=1")

[Edited on August 30, 2009 at 8:30 AM. Reason : or that might work... i dunno]

8/30/2009 8:30:24 AM

Sweet thanks!

8/30/2009 8:33:21 AM

=IF(C1>=10,IF(C1>30,"Over 30 Days!","Less than 30 Days"),"Less than 10 Days")

trying to write a nested IF statement and i havent done this in years ... Halp?

this is what i have right now but i need to add 2 more values. when i try to add more values i get either a bad formula warning or a message about too many values.

what i am trying to accomplish is the long run is a graph from a pivot table. the IF statement will produce the values for my pivot table.

the values i am looking to calculate: Over 30 Days! Over 20 Days! Over 10 Days! and Less than 10 Days!.

My problem is nesting the values the over 20 and over 10

any help would be appreciated

2/15/2011 2:00:35 PM

rofl, didn't see that gunzz jacked the thread

[Edited on February 15, 2011 at 2:09 PM. Reason : .]

2/15/2011 2:02:28 PM

=IF(C2>=30,"Over 30 Days!",IF(C2>=20,"Over 20 Days!",IF(C2>=10,"Over 10 Days","Less than 10 Days!")))

I mean, if it's 10 it's going to show up as "Over 10 Days". If you don't like that, chance the >='s to >




[Edited on February 15, 2011 at 2:10 PM. Reason : .]

2/15/2011 2:06:47 PM

^.

My only question to gunzz would be what you want to do with values = to 10. Your original formula and the mod'd one Madman supplied based off of yours will say "less than 10" when C1 is 10.

Madman with the edit! (I'm assuming what is there there is probably what gunzz meant to ask for)

[Edited on February 15, 2011 at 2:13 PM. Reason : .]

2/15/2011 2:09:25 PM

^Yeah, exactly

2/15/2011 2:09:47 PM

Why won't this work?

=IF(A1<10,"Less than 10 Days",IF(10<A1<20,"Over 10 Days",IF(20<A1<30,"Over 20 Days",IF(30<A1,"Over 30 Days",""))))

2/15/2011 2:13:16 PM

^because anytime an entry = 10, 20, or 30 it won't fit any of the criteria

2/15/2011 2:14:30 PM

=if(C1 > 10, if (C1 > 30, "Over 30 Days", if (C1 > 29, "Over 29 Days", if (C1 > 0, "1 Day or Older", Invalid Date Range))))

2/15/2011 2:14:50 PM

I wish there was a case function for excel. I hate writing these damn things.

2/15/2011 2:15:34 PM

yes, but even if it is not, i plugged it into excel with A1=9, it worked, but when a1=15 and 21, it resulted a blank....

2/15/2011 2:15:38 PM

^ah well I just glanced at it "on paper" here quick and that was the first thing that stuck out to me so that's what I said.

I would change the 10<A1<20 type sections to: AND(A1>=10,A1<20)



[Edited on February 15, 2011 at 2:20 PM. Reason : .]

2/15/2011 2:17:30 PM

thanks Madman ... i owe you a reach around.
i think i was forgetting to close a parenth ... i swear i was writing something similar and kept getting a too many values message.

anyways. /thread and i really appreciate it.

2/15/2011 2:17:42 PM

Excel doesn't like 10<A1<20 because it's two separate logical statements. To get this result, you'd have to do and(A1>10,A1<20)

2/15/2011 2:19:05 PM

Ninja'd by Madman again! (You beat my edit )

[Edited on February 15, 2011 at 2:20 PM. Reason : .]

2/15/2011 2:19:48 PM

thanks on my error.

2/15/2011 2:25:38 PM

.

[Edited on February 15, 2011 at 2:39 PM. Reason : didn't check the date of the original post]

2/15/2011 2:35:37 PM

Quote :

"a simple SUM(A1:A20) will give you the answer"

for real.

2/15/2011 2:37:44 PM