Sum by Month

pdvsa
pdvsa used Ask the Experts™
on
Hello,

I am having an issue with summing data from another sheet.
It works fine if the data is on the same sheet.

Do you see where I have made a mistake?

thank you
EE-SumIF-Dates.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Have you set the data like that or is an import? You could achieve this more efficiently by using a PivotTable if the data was arranged correctly.
pdvsaProject finance

Author

Commented:
HI Roy, I wish I could use a pivot but the original data set is not in the correct form.  What I uploaded was pieces from the original.  I would like to be able to sum per month and reference the data from the other sheet.  I would like to reference data from teh other sheet to avoid the summary sheet having too much information.
Roy CoxGroup Finance Manager

Commented:
I thought that must be the case. Is there no way to re-format the data? Maybe, just copy and pastespecial - transpose.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

pdvsaProject finance

Author

Commented:
Maybe so.  
Let me know what you think about summing the data from another sheet.

thank you
Roy CoxGroup Finance Manager

Commented:
Have you tried SUMIFS instead of SUMPRODUCT?
pdvsaProject finance

Author

Commented:
I have not...not sure how I would do that considering the data is on the other sheet.
Roy CoxGroup Finance Manager

Commented:
I've just tried and the SUMIFS works fine on the same sheet, but not with the other sheet. I'll try to find out why.
pdvsaProject finance

Author

Commented:
I just tried too and I think the range and the sum range need to be on the same sheet.  The month you are referring to (criteria) for the sum can be on the sheet with the formula.

sumif(range,criteria, [sum range])
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please try this....
On Dates Sheet,
In B17
=SUMPRODUCT(('Data TEST'!$B$1:$BJ$1<>"")*(TEXT('Data TEST'!$B$1:$BJ$1,"mmmm")=B$14)*'Data TEST'!$B$2:$BJ$2)

Open in new window

and copy across.
Roy CoxGroup Finance Manager

Commented:
This is really strange. It works perfectly when the data is arranged vertically. Note I changed the criteria to an actual date and formatted it "mmmm" on the Data Test Sheet, I prefere when working with dates to actually use real dates.
EE-SumIF-Dates--1-.xlsx
pdvsaProject finance

Author

Commented:
thank you once again.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad to help.

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 Today