SUMPRODUCT using date range

**was** · 02-21-2005, 03:59 PM

I am trying to count text in column D by month of the year. Column A contains dates mm/dd/yy. I tried the following but it returns #NA.

=SUMPRODUCT((MONTH(A5:A187=1)*(YEAR(A5:A307=2005)*(D5:D307="Approved"))))

I would like to count the number of times the word "approved" showes up in column D for January 2005.

I also read through the other postings and tried

=SUMPRODUCT(--(D5:D187="Approved"),--(A5:A187>=DATE(12/31/2004)),--(A5:A187<=DATE(2/1/2005))

Any help on this would be greatly appreciated.

**AlfD** · 02-21-2005, 08:09 PM

Hi!

I haven't actually tried the thing you are doing but I think you should try changing your original formula to:

=SUMPRODUCT((MONTH(A5:A307)=1)*(YEAR(A5:A307)=2005)* (D5:D307)="Approved")

You will notice that the ranges are now all the same size (though only you will know if it should be 307 or 187) and the expressions such as MONTH(A5:A307) are calculated and compared then with "1" or whatever.

Alf

**was** · 02-22-2005, 10:28 AM

I input the formula and it returned #VALUE. I feel you are close to the solution but we are not quite there yet. Thank you so much for the help. If you come up with another idea I would love to hear it. This is driving me nuts. I also tried the following but it returned #VALUE also.

=SUMPRODUCT(A5:A312>12/13/2004)*(A5:A312<2/1/2005)*(D2:D312="Approved")

**AlfD** · 02-22-2005, 10:46 AM

Hi!

Have a look at this recent discussion on dates and sumproduct and sumif:

http://groups-beta.google.com/group/...87063780e389f1

Alf

**was** · 02-22-2005, 11:21 AM

I read that posting and tried the following.

=SUMIF(A5:A312,">="&DATE(2004,31,12),D5:D312)-SUMIF(A5:A312,">*"&DATE(2005,1,1),(D5:D312="Approved"))

It returned 0. That is when I started playing around with SUMPRODUCT. I have been trying to figure this out for about a week. Working on it off and on durring my work day. Could you please look at the above formula? I am not very good at this and must have made a mistake. You have been very helpfull so far and I do appreciate it so much.

**AlfD** · 02-22-2005, 04:57 PM

Hi!

I reread your original post and it occurred to me that you may be using a method which might or might not be optimum.

Depends on how many times & for how many dates you want to do the count.

For example, using Data > Filter > Autofilter will give you a quick way of counting how many items satisfy your criteria. Use custom filter on the date: count the "Approved" items using the little AutoSum feature (as AutoCount, of course).

A more thorough breakdown could be done using PivotTable. To use this to count by months and years I would be inclined to insert columns which showed the month and year. If A1 holds a date, =MONTH(A1) and =YEAR(A1) will do that.
Use Year as Page: Month along one axis: Status along the other : Count of Status in the body of the table. I like this one.

BTW: DATE(Y,M,D) won't like your parameters for 31 December. And you have an asterisk in the next sumif which won't be welcome.

Alf