+ Reply to Thread
Results 1 to 8 of 8

SUMPRODUCT using date range

  1. #1
    Registered User
    Join Date
    02-21-2005
    Posts
    15

    Exclamation SUMPRODUCT using date range

    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.
    Last edited by was; 02-21-2005 at 07:23 PM. Reason: clarification

  2. #2
    Forum Contributor
    Join Date
    01-11-2004
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    388
    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

  3. #3
    Registered User
    Join Date
    02-21-2005
    Posts
    15

    Thanks for the help but it returned #VALUE

    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")

  4. #4
    Forum Contributor
    Join Date
    01-11-2004
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    388
    Hi!

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


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

    Alf

  5. #5
    Registered User
    Join Date
    02-21-2005
    Posts
    15

    Last question (hopefully)

    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.
    Last edited by was; 02-22-2005 at 11:29 AM.

  6. #6
    Forum Contributor
    Join Date
    01-11-2004
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    388
    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
    Last edited by AlfD; 02-22-2005 at 04:59 PM.

  7. #7
    Registered User
    Join Date
    02-21-2005
    Posts
    15

    Smile Thank you

    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.

  8. #8
    Forum Contributor
    Join Date
    01-11-2004
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    388
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1