SUMPRODUCT by Date and Category returns all zeros.

Michael Paxton
Michael Paxton used Ask the Experts™
on
I am trying to sum by subaccount number by month in the attached spreadsheet.  I am getting zeros in all cells.  I think I've done this numerous times in the past with no problems.  I am befuddled by this.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
No attachment
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Are your dates text that look like dates? That's a common reason why you might be seeing zero as the result of such a SUMPRODUCT formula.

If so, copy a blank cell, select your "dates" and Paste Special...Add. Then reformat to suit.
Michael PaxtonProcess Engineer

Author

Commented:
I'm fairly certain that the date format is set correctly.  I went back and tried to add them back. Deleted, set format to date and   added the dates back.  No change in output.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The procedure you described won't change text that looks like dates into real dates (numbers formatted like dates). Changing the format will never change the nature of the underlying data--and its the underlying data that SUMPRODUCT is looking at.

If you can change the appearance of a "date" cell by changing its number format, then it really is a date and not text.

If that isn't your problem, then post the formula you are trying to use--or better yet, post a workbook with a few rows of data so we can reproduce the problem.
Michael PaxtonProcess Engineer

Author

Commented:
File attachedMarketing-Budget.xlsx
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Your SUMPRODUCT formula was missing some parentheses.
=SUMPRODUCT((InvoiceDate>=Index!B$2)*(InvoiceDate<=Index!B$3)*(SubAccountName=Output!$B4))

Open in new window


Also, I changed your named ranges to start in row 2 because otherwise you would be comparing text to a date in an inequality.
Marketing-BudgetQ29165369.xlsx
Michael PaxtonProcess Engineer

Author

Commented:
Byundt,

Why is it performing a COUNT function instead of a SUM function?  What I'm looking for is the total by month by subaccount.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
There was no amount in your original formula. Do you want to add up the invoice amounts?
=SUMPRODUCT((InvoiceDate>=Index!B$2)*(InvoiceDate<=Index!B$3)*(SubAccountName=Output!$B4),InvoiceAmount)

Open in new window

Michael PaxtonProcess Engineer

Author

Commented:
Thanks for your help, Gentlemen!  I think I've just been looking at it too long!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial