Solved

Use SumIf() function to sum cells from a matrix into a new matrix

Posted on 2011-09-28
11
3,814 Views
Last Modified: 2012-05-12
I've got a worksheet that contains several hundred rows (similar to what is in rows A10:E10) on the attached spreadsheet.

What I'm looking for is a function (I think it is a combination of SumIf() and And() ) which I can use in all of the cells (same function in each cell of the range (H18:J20)) which will sum all of the values in the A10:E10 which match the values in the associated row and column headers.  Something like:

H18 = SumIf(B2:B10 = G18 and C1:E1 = H1, C2:E10)

Hope this makes sense.

I could write a function to do this, but before I go there, I thought I'd use check to see if some inventive expert could wrap this up in an already existing Excel function.

SumIf.xlsx
0
Comment
Question by:Dale Fye
[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
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36804336
The simplest way to do this is a Pivot table.

Add labels to the first two columns
select the data
Insert > Pivot table < ok
In the choose fields pane
    drag the title of the second column to the "Row Labels" box
    drag each of the titles of the dates column to the "Sigma values" box

0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36805170
If you must have a formula you can enter

=SUMIF($B:$B,$G18,C:C)

in H18 and then copy it down and across
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36813244
As you are using Excel 2007, you can use the SUMIFS function as this allows multiple criteria.

=SUMIFS(Criteria1Range,Criteria1,Criteria2Range,Criteria2,SumRange)

To be backwards compatible you would need to use SUMPRODUCT or an array formula. You can use the conditional sm wizard to set this up:

=SUM(IF(CriteriaRange1,Criteria1,CriteriaRange2,Criteria2),SumRange)

Thanks
Rob H
0
MongoDB Through a MySQL Lens

This article looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.

 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 167 total points
ID: 36813480
Sorry, got the syntax for the SUM(IF... wrong!

Should be:

=SUM(IF(CriteriaRange1,Criteria1,IF(CriteriaRange2,Criteria2,SumRange),0),0)

This should be confirmed/entered with Ctrl + Shift + Enter which will add curly brackets at the start and finish.

Thanks
Rob H
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 333 total points
ID: 36813604
Hello fyed,

I don't think either of those approaches would work here Rob, because one of the criteria applies to the header row rather than the data in the columns.

For the example given Saqib's SUMIF suggestion above should be sufficient given that the date columns are in the same order in both the data and the summary table. If that isn't the case then you can use a formula like this

=SUMIF($B$2:$B$10,$G18,INDEX($C$2:$E$10,0,MATCH(H$17,$C$1:$E$1,0)))

using INDEX and MATCH to identify the correct column to sum.......or to allow multiple columns to match the date you can use this version with SUMPRODUCT

=SUMPRODUCT(($B$2:$B$10=$G18)*($C$1:$E$1=H$17),$C$2:$E$10)

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36813652
...Sorry Rob, I should correct that, I was partially wrong - SUMIFS can't work here - because the criteria ranges and sum range all need to be the same size and shape but......

....an array SUM(IF type formula, as you suggested, will work, similar to the SUMPRODUCT version I suggested. The syntax is slightly different, though. Would need to be like this using = signs to compare

=SUM(IF(CriteriaRange1=Criteria1,IF(CriteriaRange2=Criteria2,SumRange)))

which would equate to this for the example given

=SUM(IF($B$2:$B$10=$G18,IF($C$1:$E$1=H$17,$C$2:$E$10)))

confirmed with CTRL+SHIFT+ENTER

regards, barry
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36813876
Hi Barry,

I had not looked at the sample data and had erroneously assumed that the date criteria were going to be in a column like the other criteria.

I knew (and almost said in my original comment) that you would come up with a SUMPRODUCT option.

Thanks
Rob H
0
 
LVL 18

Expert Comment

by:xtermie
ID: 36814420
Its much easier to use the subtotal feature that is build in EXCEL.
Often, there are ready made solutions within the application rather than writing elaborate formulas.
Ensure that your data is sorted by Column B as per example.

You click within the data, select Subtotals tab from the Data tab, and select that when COLUMN B changes, a SUM is needed for ALL THREE DATES available as per example.
Excel will do the rest for you.

Check the sample attached.
SumIf-v1.xlsx
0
 
LVL 48

Author Comment

by:Dale Fye
ID: 36814563
I don't consider either of the solutions provided by Barry or Rob to be "elaborate", and since I need the results in another matrix (actually located on another workbook), my preferred method will be either the SumProduct solution or the Sum(IF(IF()) solution provided.

Thanks for your feedback.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36814773
fyed, you should be able to get the formula to work on the same sheet. Once that happens you can move the table to the other workbook.
0
 
LVL 48

Author Closing Comment

by:Dale Fye
ID: 36900700
Thanks, guys.

Could have sworn that I awarded points for this the other day, but was having some internet connection issues, so it may not have gone through.

I should have thought of the nested IF's, but the SumProduct function was new to me, so I awarded more points to barry for teaching me something new (as well as actually filling in the row/column references, to help me visualize the function better.

One of these centuries, I'll be as proficient in Excel as I am in Access.

Thanks again.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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…

623 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