Solved

Sum by Month

Posted on 2016-09-03
12
40 Views
Last Modified: 2016-09-03
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
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41782805
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
 

Author Comment

by:pdvsa
ID: 41782817
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
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41782823
I thought that must be the case. Is there no way to re-format the data? Maybe, just copy and pastespecial - transpose.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:pdvsa
ID: 41782837
Maybe so.  
Let me know what you think about summing the data from another sheet.

thank you
0
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41782840
Have you tried SUMIFS instead of SUMPRODUCT?
0
 

Author Comment

by:pdvsa
ID: 41782849
I have not...not sure how I would do that considering the data is on the other sheet.
0
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41782852
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
 

Author Comment

by:pdvsa
ID: 41782858
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
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41782862
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
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41782868
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
 

Author Closing Comment

by:pdvsa
ID: 41782876
thank you once again.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41782887
You're welcome. Glad to help.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question