SUMIFS - using column & row criteria

ErinP

New Member
Joined
Apr 9, 2009
Messages
1
Is it possible to create a SUMIF formula using both a column and a row as criteria? I want to pull specific account information from one lalrge worksheet to another less detailed one. The columns include all the account numbers and the row headers show the different business units. I want to be able to pull the balance if the account # is 11111 and the business unit is ABC. I tried using the SUMIFS formula builder but got the #VALUE error.

I am not a very advanced Excel 2007 user, so any help or hints are very much appreciated.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Tony Miall

Active Member
Joined
Oct 16, 2007
Messages
304
Can you post an example of your formula and what you expect the answer to be. I'm sure SUMPRODUCT will get you out of trouble
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
ErinP

Welcome to the MrExcel board!

Not exactly sure of your layout but see if one of these helps.

If each business unit only occurs once in column A then try this:

Excel Workbook
ABCDEFGHI
11111011111111121111311114BUBu 3
2BU 154638A/c11112
3BU 265537Result4
4BU 346436
5BU 437425
6
ErinP (1)




If the business units can occur more than once, then try this:

Excel Workbook
ABCDEFGHI
11111011111111121111311114BUBu 3
2BU 354638A/c11112
3BU 265537Result10
4BU 346436
5BU 437425
6
ErinP (2)



If I have the wrong end of the stick altogether then maybe some sample data and expected results would help explain.
 

deccon

New Member
Joined
Dec 1, 2011
Messages
3
What if the Business units and accounts (a/c) occurs more than once?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881

ADVERTISEMENT

Hi deccon
Welcome to the board

Try in I2:

=SUM(IF(A2:A5=I1,IF(B1:F1=I2,B2:F5)))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.
 

Ver101

Board Regular
Joined
Sep 2, 2011
Messages
190
Can you post an example of your formula and what you expect the answer to be. I'm sure SUMPRODUCT will get you out of trouble

=SUMPRODUCT(--(A2:A5=I1)*--(I2=B1:F1)*B2:F5) here is the formula for sumproduct much faster than sumif
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

=SUMPRODUCT(--(A2:A5=I1)*--(I2=B1:F1)*B2:F5) here is the formula for sumproduct much faster than sumif
I'm not commenting on speed compared to SUMIF, but yours would be faster if it didn't double-negate evrything. ;)

=SUMPRODUCT((A2:A5=I1)*(I2=B1:F1)*B2:F5)
 

deccon

New Member
Joined
Dec 1, 2011
Messages
3
Hmm, i'm getting a #VALUE! error when I try to apply the sumproduct as Peter_SSs suggested.

I am trying to sum all the hours for a specific person for a specific project on a specific week ending. The weeks are separated into worksheets, so not a big deal there. Below are screenshots of the workbook i'm working with.

This is my summary page. The formula i'm working on is in J8. I am trying to see how many hours Manuel charged on the Project listed in B8 for Week 1. The data range is on the Q4 Week 1 worksheet and anything from F8 to BI235.

Here is the formula in J8

=SUMPRODUCT(('Q4 Week 1'!A8:A235='Project Profitability'!B8)*('Project Profitability'!J7='Q4 Week 1'!F2:BI2)*'Q4 Week 1'!F8:BI235)

Summary-1.jpg


This is the Q4 Week 1 worksheet where the data ranges are.

datafields-1.jpg


Thanks for all the quick replies on this!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
1. Check for #VALUE! errors in the range 'Q4 Week 1'!F8:BI235

2. Not really an error, but since you SUMPRODUCT formula in on the sheet 'Project Profitability' there is no need to qualify references to ranges on that sheet in your formula. So it could be this

=SUMPRODUCT(('Q4 Week 1'!A8:A235=B8)*(J7='Q4 Week 1'!F2:BI2)*'Q4 Week 1'!F8:BI235)

3. If you are still having a problem, consider constructing a much smaller sample and posting small screen shots directly in your post. My signature block has suggestuions. The images you have shown are not much use since the data cannot be copied to test with and you won't get many people wanting to type out that much! In any case, if they did type it, it may not accurately reflect your data.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
=SUMPRODUCT(('Q4 Week 1'!A8:A235=B8)*(J7='Q4 Week 1'!F2:BI2)*'Q4 Week 1'!F8:BI235)

This will return a #VALUE! error if there is any text in the sum range (even "null strings" returned by formulas), you can avoid that with a subtle change, replacing the 2nd * with a comma, i.e.

=SUMPRODUCT(('Q4 Week 1'!A8:A235=B8)*(J7='Q4 Week 1'!F2:BI2),'Q4 Week 1'!F8:BI235)

That should give you a numeric result (as would pgc's array version) ....but you might want to understand where you might have text in that range......
 

Watch MrExcel Video

Forum statistics

Threads
1,122,494
Messages
5,596,486
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top