COUNT.IF, how to include single cells

[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

 

[Domenic]

Try...

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

Hope this helps!

 

[Bob Phillips]

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)

 

[Domenic]

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?

 

[Guest]

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:

 

[Domenic]

Try...

=SUMIF(N120:N350,"Category",H120:H350)

Hope this helps!

 

[Bob Phillips]

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)

 

[Domenic]

Okay...it's clearly not going to be my day. Make that...

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

 

[Guest]

Thanks all for your guidance! It worked fine!

Anders

"Bob Phillips" skrev: