Solved

Sumifs with more than one column as sum range

Posted on 2015-01-27
6
144 Views
Last Modified: 2015-02-24
Hi Experts,

I have an excel spreadsheet with three tabs: Summary, Data and Settings.

In the summary tab i want to use a a formula that shows balances across the columns per each account in each row. The formula will read from the data tab where I have define names for each column as Per1, Per2, Per3.

so, the summary tab will return Bal_2 as the sum of Per1 and Per2; Bal_3 as the sum of Per1...Per3

I have used sum products and sumifs, but I think the issue I have is that sumifs won't sum range more than one column. See my spreadsheet  attached.
test.xlsx
0
Comment
Question by:Pachecda
[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 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40574114
you made that over complicated with so many confusing named ranges.

here is the solution you need. see attached.
test.xlsx
0
 

Author Comment

by:Pachecda
ID: 40574155
HI ProfessorJimjam.

I saw the spreadsheet with the updated formulas and I see that your solution is adding the current period plus the previous column balance as seen in the "+sum(C2:C2)". However, I was hoping for a more dynamic array as the formula below is dependent upon the previous column, which it may not work if the defined names are shuffled around the columns.

Your suggested formula: SUMPRODUCT(INDIRECT(D$1),--ISNUMBER(MATCH(Account,$A2,0)))+SUM(C2:C2)

In theory, I was thinking to have a defined name Bal_2 (_Per_1+_Per_2)

Bal_2 = Per_1+Per_2
Bal_3=Per_1+Per_2+Per_3
....etc
I sure hope there is another way where the balances are not relying on a previous column.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40575084
See attached.

On Data tab I have added 12 columns to the right which is the cumulative total to date for each row.

The summary tab then uses a SUMIF formula on account and sums the individual cumulative columns.

If your headers were dates rather than Period headings, you could use a two directional SUMIFS, ie look top to bottom in column A for Account number and look left to right in lets say row 1 for a date and only add up where less than or equal to specified date for column in Summary. I think that would work. Edit: Scrap that bit, just tried and wouldn't work.

Hope this helps.
Rob H
test1.xlsx
0
 

Accepted Solution

by:
Pachecda earned 0 total points
ID: 40575544
Hi Rob,

Thanks for your help on this. Although creating the additional twelve columns for cumulative balances works, I was hoping to be able to this via an array and defined names rather than relying on additional twelve columns for cumulative balances.

Last night I took a different approach and used the array formula {=sum(if((Accounts=account),Bal_2))} where "Bal_2" is a defined name with the sum of Indirect (_Per_1)+indirect(_Per_2).  The formula works like a charm, the only issue is that now I want to make the "Bal_2"  to be referenced to another cell where the user chooses what balance to analyse, and I can't seem to make it work.

Then I thought, if the array formula above works, why wouldn't the sumproduct or the sumifs work. So, I created two additional summary tables right below the array formulas for comparison and it seems two work for Bal_1, but not for the other balances.

Please see the attached file and you will see that I tried to compare the three different formulas using the same approach with the defined names.
test2.xlsx
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40627777
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

696 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