Microsoft Excel 2003 (full product)

I am looking to create a formula that will return a count based off of two other criteria. For example I have an Excel spreedsheet that has three columns (Date, Field, & Count) Date Field Count

1/1/2010 A 0

1/1/2010 B 60

1/1/2010 C 800

1/2/2010 A 2

1/2/2010 B 0

1/2/2010 C 55

1/3/2010 A 5

1/3/2010 B 0

1/3/2010 C 60I would like my table to be return the following:

Date 1/1/2010 1/2/2010 1/3/2010

Field

A 0 1 1

B 1 0 0

C 1 1 1The actual data set would have more than 1 count per day.

Please help. Thank you

Hi, Assuming that your sample data table is in cells A1 to C9

then you can achieve the following:A B C D 11 Date 01/01/10 01/02/10 01/03/10 12 Field 13 A 0 2 5 14 B 60 0 0 15 C 800 55 60Enter the following formula in cell B13:

=SUMPRODUCT(($A$1:$A$9=B$11)*($B$1:$B$9=$A13)*($C$1:$C$9))

Drag to extend the formula through to cell D15

Note the $ signs - they are essential for the formula to be extended properly by dragging or copy/pasting.From what you said, I assumed that you wanted the total counts not just 1 or 0 as shown in your post. If you wanted to return zero if the count was zero, or 1 if the count was not zero, let me know and the formula can be modified.

Without having real data to work on I cannot test that this will work with your data. If it does not work 'as expected' please post some real data with the expected results, and information on what was returned by the above formula.

It may be better to format dates with text for the month, to help ensure that apparently similar dates are actually the same.

1/2/10 : 01 Feb 2010 or 02 Jan 2010

Regards

Ask Your Question

Weekly Poll