Avatar of Michael Paxton
Michael Paxton
Flag for United States of America asked on

SUMPRODUCT by Date and Category returns all zeros.

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.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Michael Paxton

8/22/2022 - Mon
Rob Henson

No attachment
byundt

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 Paxton

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
byundt

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 Paxton

ASKER
File attachedMarketing-Budget.xlsx
byundt

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Michael Paxton

ASKER
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.
ASKER CERTIFIED SOLUTION
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Michael Paxton

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