# Count if formula with multiple criteria

Microsoft Excel 2003 (full product)
February 16, 2010 at 13:47:34
Specs: Windows XP
 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 Count1/1/2010 A 01/1/2010 B 601/1/2010 C 8001/2/2010 A 21/2/2010 B 01/2/2010 C 551/3/2010 A 51/3/2010 B 01/3/2010 C 60I would like my table to be return the following: Date 1/1/2010 1/2/2010 1/3/2010Field 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

See More: Count if formula with multiple criteria

#1
February 16, 2010 at 14:36:10
 Hi,Assuming that your sample data table is in cells A1 to C9then 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 60 ```Enter 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 D15Note 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

Report •
Related Solutions