formula help

Topic

New Reply

Need some help please. Enclosed is a small spreadsheet that I have been working on. I can not get a count function to work properly in Z13. I wish to count the number of entries in column N, if column E=1. The correct answer should be 5. I keep getting a total count or zero. I am sure I am missing the obvious here. Could someone help?
Thank You!

Scott Vander Sande

bears@new.rr.com

Reply | Quote

Replies

Hi Scott,
I am assuming you only want to count the cells in N with a value where the corresponding cell in E has a 1? (in which case the answer is 3 not 5 by my reckoning). If so then type:
=SUM(IF(E1:E12=1,IF(N1:N12″”,1,0),0)) into N13 and array-enter it by pressing Ctrl-Shift-Enter simultaneously.
Otherwise you’re just counting the cells in E that have 1 in them ( =countif(E1:E12,1) )
Hope that helps?

Reply | Quote

Try this:

=SUM((E2:E17=1)*(ISBLANK(N2:N17)=FALSE))

I counted and got 6

Reply | Quote

I get 6 entries.
=SUMPRODUCT(N(N1:N10000>0),N(E1:E10000=1))

Reply | Quote

Thanks for the help everyone. I got to see three different approaches that I had never seen before.

Scott

Reply | Quote