I'm getting really confused....

I need to count how many times a number appears if it has a specific word
written next to it....for example....

A B
1 1 Summer 1
2 2 Summer 2
3 1 Autumn 1
4 3 Autumn 2

I need to count how many times the number 1, 2 and 3 appear next to Autumn 2
in the table..... does that make any sense?
I've tried using the Dcount function and either I'm not doing it right, or
it doesn't work for this... The countif formula works if I don't need to
specify summer or autumn, but in this case I do...
Can anyone help?...Please? It's driving me nuts!
Thanks

Hi,

=SUMPRODUCT((A1:A22=ROW(A1))*(B1:B22="Summer 1"))

Put this in a cell and drag down 2 rows
Row 1 = 1 and summer 1
Row 2 = 2 and summer 1
etc

Mike

"Scoob_a" wrote:

> I'm getting really confused....
>
> I need to count how many times a number appears if it has a specific word
> written next to it....for example....
>
> A B
> 1 1 Summer 1
> 2 2 Summer 2
> 3 1 Autumn 1
> 4 3 Autumn 2
>
> I need to count how many times the number 1, 2 and 3 appear next to Autumn 2
> in the table..... does that make any sense?
> I've tried using the Dcount function and either I'm not doing it right, or
> it doesn't work for this... The countif formula works if I don't need to
> specify summer or autumn, but in this case I do...
> Can anyone help?...Please? It's driving me nuts!
> Thanks

Separately?

=SUMPRODUCT(--(A1:A10=1),--(B1:B10="Autumn 2"))

etc.

Or all at once?

=SUMPRODUCT((A1:A10={1,2,3})*(B1:B10="Autumn 2"))

--
__________________________________
HTH

Bob

"Scoob_a" <(E-Mail Removed)> wrote in message
news:580531BA-8B43-47E5-964E-(E-Mail Removed)...
> I'm getting really confused....
>
> I need to count how many times a number appears if it has a specific word
> written next to it....for example....
>
> A B
> 1 1 Summer 1
> 2 2 Summer 2
> 3 1 Autumn 1
> 4 3 Autumn 2
>
> I need to count how many times the number 1, 2 and 3 appear next to Autumn
> 2
> in the table..... does that make any sense?
> I've tried using the Dcount function and either I'm not doing it right, or
> it doesn't work for this... The countif formula works if I don't need to
> specify summer or autumn, but in this case I do...
> Can anyone help?...Please? It's driving me nuts!
> Thanks

Try this:

=SUMPRODUCT((A1:A4=1)*(B1:B4="Autumn2))

Adjust the ranges to suit, but you can't use full-column references
(unless you are using XL2007).

Change the =1 in the first term to get counts for the other values in
column A, or you could use a cell to hold the values.

Hope this helps.

Pete



On Jun 19, 12:59*pm, Scoob_a <Sco...@discussions.microsoft.com> wrote:
> I'm getting really confused....
>
> I need to count how many times a number appears if it has a specific word
> written next to it....for example....
>
> * * *A * * * * B
> 1 * *1 * * *Summer 1
> 2 * *2 * * *Summer 2
> 3 * *1 * * *Autumn 1
> 4 * *3 * * *Autumn 2
>
> I need to count how many times the number 1, 2 and 3 appear next to Autumn 2
> in the table..... does that make any sense?
> I've tried using the Dcount function and either I'm not doing it right, or
> it doesn't work for this... The countif formula works if I don't need to
> specify summer or autumn, but in this case I do...
> Can anyone help?...Please? *It's driving me nuts!
> Thanks

Brilliant! My brain is happy again!

"Bob Phillips" wrote:

> Separately?
>
> =SUMPRODUCT(--(A1:A10=1),--(B1:B10="Autumn 2"))
>
> etc.
>
> Or all at once?
>
> =SUMPRODUCT((A1:A10={1,2,3})*(B1:B10="Autumn 2"))
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Scoob_a" <(E-Mail Removed)> wrote in message
> news:580531BA-8B43-47E5-964E-(E-Mail Removed)...
> > I'm getting really confused....
> >
> > I need to count how many times a number appears if it has a specific word
> > written next to it....for example....
> >
> > A B
> > 1 1 Summer 1
> > 2 2 Summer 2
> > 3 1 Autumn 1
> > 4 3 Autumn 2
> >
> > I need to count how many times the number 1, 2 and 3 appear next to Autumn
> > 2
> > in the table..... does that make any sense?
> > I've tried using the Dcount function and either I'm not doing it right, or
> > it doesn't work for this... The countif formula works if I don't need to
> > specify summer or autumn, but in this case I do...
> > Can anyone help?...Please? It's driving me nuts!
> > Thanks
>
>
>