Sum by Month

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
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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.
0
pdvsaProject financeAuthor 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.
0
Roy CoxGroup Finance ManagerCommented:
I thought that must be the case. Is there no way to re-format the data? Maybe, just copy and pastespecial - transpose.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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

thank you
0
Roy CoxGroup Finance ManagerCommented:
Have you tried SUMIFS instead of SUMPRODUCT?
0
pdvsaProject financeAuthor Commented:
I have not...not sure how I would do that considering the data is on the other sheet.
0
Roy CoxGroup Finance ManagerCommented:
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.
0
pdvsaProject financeAuthor 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])
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
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
0
pdvsaProject financeAuthor Commented:
thank you once again.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.