COUNT.IF, how to include single cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 400 rows of data. One column has the text "Y" or "N". I want to
calculate the number of "Y" with COUNT.IF. However I don't want to include
alla rows in the calculation, only some specific ones, ex H120, H156, H198
etc.

I can only get this to work with a full unbroken range (ex H120:H350)

How should I do it?

Rgds
Anders
 
Try...

=SUMPRODUCT(--(MOD(ROW(H120:H350)-ROW(H120)+0,36)=0),--(H120:H350="Y"))

Hope this helps!
 
Anders,

Is there some criteria that you can latch onto, such as a value in another
column?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Ooops! I incorrectly assumed that you wanted to sum every 36th cell.
So my formula won't give the results you desire. As Bob has asked, is
there some criteria involved?
 
Yes,

That is actually why i want to exclude some specific rows. I want to have
the number of "Y" in the H column by the category presented in the N column.

Can you make a general formula for this?

Thx a lot!

Anders

"Bob Phillips" skrev:
 
Hi Anders,

Yes, that is a nice easy one :-)

=COUNTIF(N:N="category",H:H)

if there are other values in H other than Y for that category, then try

=SUMPRODUCT(--(N1:N200="category"),--(H1:H200="Y"))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Okay...it's clearly not going to be my day. :) Make that...

=SUMPRODUCT(--(H120:H350="Y"),--(N120:N350="Category"))
 
Back
Top