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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
=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)
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
=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......
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top