COUNT.IF, how to include single cells

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!

In article <[email protected]>,
Anders <[email protected]> wrote:

> 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

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)


"Anders" <[email protected]> wrote in message
news:[email protected]...
> 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

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?

In article <[email protected]>,
Domenic <[email protected]> wrote:

> Try...
>
> =SUMPRODUCT(--(MOD(ROW(H120:H350)-ROW(H120)+0,36)=0),--(H120:H350="Y"))
>
> Hope this helps!
>
> In article <[email protected]>,
> Anders <[email protected]> wrote:
>
> > 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

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:

> 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)
>
>
> "Anders" <[email protected]> wrote in message
> news:[email protected]...
> > 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...

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

Hope this helps!

In article <[email protected]>,
Anders <[email protected]> wrote:

> 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:
>
> > 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)
> >
> >
> > "Anders" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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)


"Anders" <[email protected]> wrote in message
news:[email protected]...
> 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:
>
> > 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)
> >
> >
> > "Anders" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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

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

In article <[email protected]>,
Domenic <[email protected]> wrote:

> Try...
>
> =SUMIF(N120:N350,"Category",H120:H350)
>
> Hope this helps!

Thanks all for your guidance! It worked fine!

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)
>
>
> "Anders" <[email protected]> wrote in message
> news:[email protected]...
> > 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:
> >
> > > 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)
> > >
> > >
> > > "Anders" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>