Count text on certain date
 

I need help to get system to get result by counting. for example based on the
'Data' below, there's a row of dates in column A and currency in Column B.

In a summary page, I have these

Date
6/3/08 -- The questions is, how do I fomulate the cell to count for me the
number of AUS for the said date?


Data
****
A B
------------------
6/3/08 AUS
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 AUS
6/3/08 DMK
6/3/08 OSM
6/3/08 OSM

Count text on certain date
 

Try this...

Use cells to hold the criteria:

D1 = 6/3/08
E1 = AUS

=SUMPRODUCT(--(A1:A11=D1),--(B1:B100=E1))

--
Biff
Microsoft Excel MVP


"Jafferi" wrote in message
...
I need help to get system to get result by counting. for example based on
the
'Data' below, there's a row of dates in column A and currency in Column B.

In a summary page, I have these

Date
6/3/08 -- The questions is, how do I fomulate the cell to count for me
the
number of AUS for the said date?


Data
****
A B
------------------
6/3/08 AUS
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 AUS
6/3/08 DMK
6/3/08 OSM
6/3/08 OSM

Count text on certain date
 

I tried but the results I got is #NUM!. Did I do something wrong?


"T. Valko" wrote:

Try this...

Use cells to hold the criteria:

D1 = 6/3/08
E1 = AUS

=SUMPRODUCT(--(A1:A11=D1),--(B1:B100=E1))

--
Biff
Microsoft Excel MVP


"Jafferi" wrote in message
...
I need help to get system to get result by counting. for example based on
the
'Data' below, there's a row of dates in column A and currency in Column B.

In a summary page, I have these

Date
6/3/08 -- The questions is, how do I fomulate the cell to count for me
the
number of AUS for the said date?


Data
****
A B
------------------
6/3/08 AUS
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 AUS
6/3/08 DMK
6/3/08 OSM
6/3/08 OSM

Count text on certain date
 

I tried but the results I got is #NUM!.
Did I do something wrong?

Yes, you probably did this:

=SUMPRODUCT(--(A:A=D1),--(B:B=E1))

If you're not using Excel 2007 you can't use entire columns as range
references. Use a smaller specific range.

I see I had a typo in my previous reply:

=SUMPRODUCT(--(A1:A11=D1),--(B1:B100=E1))

That should have been:

=SUMPRODUCT(--(A1:A11=D1),--(B1:B11=E1))


--
Biff
Microsoft Excel MVP


"Jafferi" wrote in message
...
I tried but the results I got is #NUM!. Did I do something wrong?


"T. Valko" wrote:

Try this...

Use cells to hold the criteria:

D1 = 6/3/08
E1 = AUS

=SUMPRODUCT(--(A1:A11=D1),--(B1:B100=E1))

--
Biff
Microsoft Excel MVP


"Jafferi" wrote in message
...
I need help to get system to get result by counting. for example based
on
the
'Data' below, there's a row of dates in column A and currency in Column
B.

In a summary page, I have these

Date
6/3/08 -- The questions is, how do I fomulate the cell to count for me
the
number of AUS for the said date?


Data
****
A B
------------------
6/3/08 AUS
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 DMK
6/3/08 AUS
6/3/08 DMK
6/3/08 OSM
6/3/08 OSM