Link to home
Create AccountLog in
Avatar of Michael Vasilevsky
Michael VasilevskyFlag for United States of America

asked on

Using SUMPRODUCT with a Date Range

I'm trying to sum the number of dates with in a range for a given document type using SUMPRODUCT in the attached example spreadsheet.

I can get the formula to return the correct value for a single date, but I want to sum over a range of dates, specifically the entire week.

The formula is in Summary!C2. I thought =SUMPRODUCT(--(Dates!A2:A392=A2),--(Dates!B2:B392=C1), --(Dates!B2:B392=C1+1), ...) would work but that returns 0.

What am I missing?

Thank you!
Test-Spreadsheet-MV.xlsx
Avatar of Shums Faruk
Shums Faruk
Flag of India image

You may try =COUNTIFS(Dates!$B:$B,"="&D$1,Dates!$A:$A,$A2)
Test-Spreadsheet-MV_v1.xlsx
SUMPRODUCT is a function used to add (SUM) a range of multiplications (PRODUCTS)
How are you planning on multiplying dates?

I thought long and hard before replying - I don't think I am missing something?  Your source data are dates - SUMPRODUCT won't apply!
Avatar of Arana (G.P.)
Arana (G.P.)

I think you want to COUNT the dates and not SUM (add them)
I already noticed and gave him CountIF option
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Michael Vasilevsky

ASKER

Thank you!