3 green in March, 0 in February, 12 in January...
November 10, 2010 7:59 AM Subscribe
Yet another excel question - month + text criteria counting...
I am trying to put together an excel sheet that will automatically tally a count of all rows (entries) in a table that meet two categories. In my table, I have the date of each entry (column A) as m/d/yyyy t:tt, then the color of each item as column b as text ("red" "grn" "ylw", etc).
I want to create a report to show the number of yellows from may vs april, march, etc. (and under that, I'll copy the formula, change the text filter for blue, red, green, etc)
I've googled two variable countifs, but that doesn't seem to help, and when I created a pivot table, I created it by color, then filtered by date, grouping by month. However, when I don't have any of that color for that month, I can't seem to get it to show me a value of 0.
How do I either show the monthly total of 0 in a pivot table, or use a count/sum/etc function to pull the data directly from the table?
I am trying to put together an excel sheet that will automatically tally a count of all rows (entries) in a table that meet two categories. In my table, I have the date of each entry (column A) as m/d/yyyy t:tt, then the color of each item as column b as text ("red" "grn" "ylw", etc).
I want to create a report to show the number of yellows from may vs april, march, etc. (and under that, I'll copy the formula, change the text filter for blue, red, green, etc)
I've googled two variable countifs, but that doesn't seem to help, and when I created a pivot table, I created it by color, then filtered by date, grouping by month. However, when I don't have any of that color for that month, I can't seem to get it to show me a value of 0.
How do I either show the monthly total of 0 in a pivot table, or use a count/sum/etc function to pull the data directly from the table?
Response by poster: Thanks for the response, but when I did this, it didn't actually add the months - I don't have empty cells in the table, it just doesn't show the no-result months in the pivot-table
posted by Seeba at 9:17 AM on November 10, 2010
posted by Seeba at 9:17 AM on November 10, 2010
Best answer: In yet another poorly-documented feature of Excel, you can do multiple conditions with SUMPRODUCT. Try something like this:
=SUMPRODUCT(1*(MONTH(A1:A999)=5)*(B1:B999="ylw"))
This should total up all the yellow entries from May. If your dates come from different years, you'll have to be a little more delicate.
This works in Excel 2007, but I'm not sure if it works in earlier versions.
posted by mhum at 9:37 AM on November 10, 2010 [2 favorites]
=SUMPRODUCT(1*(MONTH(A1:A999)=5)*(B1:B999="ylw"))
This should total up all the yellow entries from May. If your dates come from different years, you'll have to be a little more delicate.
This works in Excel 2007, but I'm not sure if it works in earlier versions.
posted by mhum at 9:37 AM on November 10, 2010 [2 favorites]
I'm not at a computer with Excel 2007 right now, but I think a COUNTIFS formula would do the trick (you'd just have to adjust the criteria accordingly for each count).
http://office.microsoft.com/en-us/excel-help/use-the-countifs-function-in-excel-2007-to-analyze-data-HA010236993.aspx
posted by Kattiara17 at 9:44 AM on November 10, 2010
http://office.microsoft.com/en-us/excel-help/use-the-countifs-function-in-excel-2007-to-analyze-data-HA010236993.aspx
posted by Kattiara17 at 9:44 AM on November 10, 2010
This thread is closed to new comments.
posted by ghharr at 8:04 AM on November 10, 2010