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

**was** · 02-22-2005, 07:33 PM

I really do appreciate the help. It's nice to know that if I'm really in a crunch there are people who will take the time to help.
I resolved the problem with the following formula.

=SUMPRODUCT(--(LIST!D5:D314="Approved"),--(LIST!A5:A314>=LIST!K6),--(LIST!A5:A314<=LIST!K7))

It returned a 0 when I tried it with the date in the formula. When I put the start and end dates in cells and then refrenced the cells in the formula it worked. If anyone knows why the dates don't work in the formula I'd sure like to know.

I have never created a Pivot table before. I think I'll try that next.

**AlfD** · 02-22-2005, 08:23 PM

Hi!

Actually, you sorted it yourself. Well done!

Pivot tables must rank among Excel's most powerful features. Worth a try just to add such a weapon to your armoury

Some pessimist once said a chain is as strong as its weakest link. True, I suppose. But a group is as strong as its strongest element. What a difference! And there are some very strong elements in this group ( don't count me in that).

Alf