count non blank cells with condition

[ec1217]

how to count non blank cells with condition?

situation:
Column A (Name)
Column B (Division - man,woman,young man, young woman)
Column C (quantity of subscription - number)

*some of them may subscribe more than 1 magazine

just wanna find out how many of young woman did subscribe the magazine (not the quantity of magazine)


so what formula i can use? anyone can help me please~

 

[alow]

Are you looking to count Non Blank cells in Column C? if so, you would use =COUNTA(C1:C100) and just change the range to include the rows you want to check in.

 

[ec1217]

yea im looking for non blank cells in column C.

izit possible if i don want change the range to the rows? i mean can i fix the formula and just change the criteria to "man" or "woman"?

cz i would like to apply this formula to different sheet to find out the number.

 

[alow]

I don't quite understand. If you want to count non-blanks in C1 through C100 you would use the formula above (=COUNTA(C1:C100)). If your list went from C2 through C2067 the formula would be =COUNTA(C2:C2067). Are you looking to count for how many of each division have a non blank in column C? If so, to do it easily, requires Excel 2007. Which version do you have? If you have an earlier version, it's doable, just not easily. If you could try to explain it more in depth, I'd be glad to build a formula for you.

 

[ec1217]

Thanks alow, but really very sorry that actually my English not good that's why i couldnt explain it deeply.

like this, in C1:C100 is the quantity of subscription, B1:B100 is the division without sorting.

A B C
1 Aaron man 2
2 Annie woman 0
3 Ellie woman 1
4 Adam man 2
5 Abby woman 1


how do i get the answer that only 2 woman subscribe but not the total number of the subscription?

 

[alow]

Which version of Excel do you have? 2003, 2007, or 2010? If you're not sure, just tell me what menus you have across the top and I can figure it out. The formula to use is different depending on the version.

 

[ec1217]

2007.

 

[alow]

You would use the following for man:
=COUNTIFS($b$1:$b$100,"man",$c$1:$c$100,"<>")

This will only work if the cells in column C are actually blank, if there are zeros there, it should be
=COUNTIFS($b$1:$b$100,"man",$c$1:$c$100,"<>0")

Let me know how that works for you.

 

[ec1217]

YEAHHHHHHHH!!!! it's work!!!!! thank you very much!!!!!!
u save me!!!!

btw can i know what is "<>"?

 

[alow]

<> in Excel means "Not Equal". You could probably use <>"" to mean "Not Equal Blank," but in this formula, you only need to use <> in quotes. Glad it works for you!

 

[ec1217]

ic, thank you soooooo much, i learn a lot..... u really pro..

btw izit a faster way to copy the formula to other cells? cz when i just simply click the cells n copy paste like that the formula range will change automatically.

exp:
=COUNTIFS(E4:E33,"MD",Z4:Z33,"<>")

when i click the cell n copy, it will bcm

=COUNTIFS(F4:F33,"MD",AA4:AA33,"<>")

what i want is
=COUNTIFS(E4:E33,"MD",AA4:AA33,"<>")


and what im currently doing is click on the formula bar change it one by one....

 

[alow]

You use Dollar Signs in front of the References you want to stay the same, so you would use
=COUNTIFS($E$4:$E$33,"MD",Z4:Z33,"<>")
And the reference to Column E would stay and the reference to Column Z would change to AA as you drag the formula to the right.

 

[ec1217]

oh man i should ask this question earlier, cz all the way im doing this one by one like idiot....

Lastly a big thanks to u.....

 

[alow]

No problem. I'm glad it worked for you. It's hard to ask the right question if you don't know where to begin.