Hi

This question has probably been asked (and answered) a million times...

I have the following sheet
A B
Peas 1 Aug
Peas 2 Aug
Beans 1 Aug
Peas 2 Aug
Beans 2 Aug

I want a formula that can tell me how many times the word "Peas" appears for
a specific date (eg 1 Aug).

My worksheet has hundreds of rows of information and I do not want sort. I
want to type in the word "Peas" and it should give a total.

Thanks in advance

Hi,
let's assume you enter the name Peas or what you want to count in cell D2
and the date you want in D3 then you want the result in E4 do there enter

=SUMPRODUCT((D2=A1:A5)*(D3=B1:B5))

change range to fit your needs but remember it has to be the same in both
parts of the formula

if this helps please click yes, thanks

"Greggo G" wrote:

Hi

This question has probably been asked (and answered) a million times...

I have the following sheet
A B
Peas 1 Aug
Peas 2 Aug
Beans 1 Aug
Peas 2 Aug
Beans 2 Aug

I want a formula that can tell me how many times the word "Peas" appears for
a specific date (eg 1 Aug).

My worksheet has hundreds of rows of information and I do not want sort. I
want to type in the word "Peas" and it should give a total.

Thanks in advance

Let's say the date you want to look for is in C2, "Peas" is in C3
=SUMPRODUCT(--(A2:A10=C3),--(B2:B10=C2))

Increase array sizes as needed. Note that you can't callout entire columns
(A:A) in SUMPRODUCT unless using XL 2007.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Greggo G" wrote:

Hi

This question has probably been asked (and answered) a million times...

I have the following sheet
A B
Peas 1 Aug
Peas 2 Aug
Beans 1 Aug
Peas 2 Aug
Beans 2 Aug

I want a formula that can tell me how many times the word "Peas" appears for
a specific date (eg 1 Aug).

My worksheet has hundreds of rows of information and I do not want sort. I
want to type in the word "Peas" and it should give a total.

Thanks in advance