Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sum multiple columns based on multiple row criteria

Posted on 2016-09-08
16
Medium Priority
?
432 Views
Last Modified: 2016-09-09
I have an unusually formatted data source and need to work out how to calculate a total of multiple rows and columns based on multiple criteria. A small sample is attached.

Basically the user needs to be able to choose a Location Id; A Day Qtr; and a Week Number (yellow cells at the top of my sample) and the total and average at the top needs to calculate the results (to make it clearer the total and average shown in this example would be based on the yellow cells in the sheet).

Ideally I need to be able to do it without adding extra columns because the data is extracted every week from another system in the format shown commencing at Row 6 so I would like the user to be able to simply paste the new report over the top.

Can anyone help me figure out how to calculate the total given it needs to sum multiple rows and columns?
Demo.xlsx
0
Comment
Question by:Rob4077
[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
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 27

Expert Comment

by:ProfessorJimJam
ID: 41789442
ok based on the condition, where is that 5 coming from?  if the criteria matches then what amount needs to be added.

you are not clear about from where amount needs to be calculated from
0
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41789461
Try this....

In C5
=SUMPRODUCT(($B$8:$B$19+0=$C$1)*($D$8:$D$19+0=$C$2)*($E$6:$T$6=$C$3)*$E$8:$T$19)

Open in new window

0
 

Author Comment

by:Rob4077
ID: 41789465
Sorry. In this case on:
3.1.2016, location Id 23456 had 5 units in DayQtr 3,
4.1.2016, location Id 23456 had 25 units in DayQtr 3,
5.1.2016, location Id 23456 had 22 units in DayQtr 3,
6.1.2016, location Id 23456 had 24 units in DayQtr 3,
7.1.2016, location Id 23456 had 33 units in DayQtr 3,
8.1.2016, location Id 23456 had 26 units in DayQtr 3,
9.1.2016, location Id 23456 had 14 units in DayQtr 3,

That entire week data related to week 2 so in this case, if the user chose week 2, LocationId 23456 and DayQtr 3 the Average is 7 and the Total is 149
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Rob4077
ID: 41789472
With regard to =SUMPRODUCT(($B$8:$B$19+0=$C$1)*($D$8:$D$19+0=$C$2)*($E$6:$T$6=$C$3)*$E$8:$T$19)
What is the significance of the +0 in each array?
0
 

Author Comment

by:Rob4077
ID: 41789475
Also, is there a way of referring to an entire column $B:$B without including the row number? That would make it much easier to do the regular data updates, or am I locked in to including the row?
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41789477
Because your location ids in col. B and qtr# in col. D are numbers formatted as Text while your criteria are real numbers.
To convert these numbers formatted as Text in your data set, a 0 is added.
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41789493
Referencing the whole column is not advisable in the sumproduct formula.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41789509
@Rob4077 - I assume your comment about including all of column B also applies to including further columns with updated data. Columns E to T are 1 to 16 Jan, I assume 17 Jan will go in col U, 18 Jan col V etc.

With a different layout of the data, this would be possible with a pivot table.
0
 

Author Comment

by:Rob4077
ID: 41789517
Thanks Rob for your observation but unfortunately the data is inflexible and my requirement very specific so I can't use a pivot table.
Neeraj, your solution is exactly what I need. Thanks for the extra clarification
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41789525
You're welcome Rob!
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41789526
In which case before pasting in new data, insert a column before column T, thus pushing the reference to column T in the formula to column U.

Alternatively, you could use formula to convert the data from the existing format to a format that can be used in a Pivot.

Thanks
Rob
0
 

Author Comment

by:Rob4077
ID: 41789559
Oops, forgot to follow up. How do I work out the average?
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41789575
Like this...

=SUMPRODUCT(($B$8:$B$19+0=$C$1)*($D$8:$D$19+0=$C$2)*($E$6:$T$6=$C$3)*$E$8:$T$19)/SUMPRODUCT(($B$8:$B$19+0=$C$1)*($D$8:$D$19+0=$C$2)*($E$6:$T$6=$C$3))

Open in new window

0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41789589
Carrying on with the Pivot Table suggestion, with data formatted/converted by formula from the original format, see attached.

The only addition to the Original data is the concatenation in column A, making each row unique.

The table on Formatted then pulls from the Original data; to update copy and paste the last 12 rows (yellow) to the bottom of the table. I suspect this is not truly representative as there will no doubt be more than 3 location IDs. Columns A to D are basically a copy of the row headers from Original repeated for each daily batch of transactions. Columns E to G are formula driven.

Pivot sheet will update by Refresh, the Data Source will update automatically because the data on Formatted is set as a table.
Demo.xlsx
0
 

Author Comment

by:Rob4077
ID: 41790502
Hi Rob, Thanks for the suggestion and demo. I see what you're getting at and it certainly will accomplish what I asked. Trouble is that my question was simplified to just the first step of where I need to get to and, unfortunately, the pivot table is unlikely to fit the total solution.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41791333
No worries.

When doing bigger projects in Excel, I have often found it better to work backwards from what the desired end result is and then amend the source data where possible or include a step to adapt the data so that it fits.

However, more often than not there are already various parts in place that people want to keep and need working round.

Thanks
Rob H
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

598 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