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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi, I have tried this but it works in same sheet, but it didn't work when summary in different sheet other than Dashboard sheet.=SUMIFS(INDEX($B$4:$E$11,0,MATCH($J$3,$B$3:$E$3,0)),$A$4:$A$11,$H5) tried with in same sheet and it works.=SUMIFS(INDEX(Sheet2!$B$2:$E$9,0,MATCH($J$3,Sheet2!$B$1:$E$1,0)),Sheet2!$A:$A,$H$5) this didn't work.guide me where it is getting struck..
 
Upvote 0
Hi, I have tried this but it works in same sheet, but it didn't work when summary in different sheet other than Dashboard sheet.=SUMIFS(INDEX($B$4:$E$11,0,MATCH($J$3,$B$3:$E$3,0)),$A$4:$A$11,$H5) tried with in same sheet and it works.=SUMIFS(INDEX(Sheet2!$B$2:$E$9,0,MATCH($J$3,Sheet2!$B$1:$E$1,0)),Sheet2!$A:$A,$H$5) this didn't work.guide me where it is getting struck..
The problem is that you have significantly changed the formula when changing from one to the other.


=SUMIFS(INDEX($B$4:$E$11,0,MATCH($J$3,$B$3:$E$3,0)),$A$4:$A$11,$H5)
In the formula above you have (correctly) made the two ranges the same size, 8 rows - from 4 to 11.

In the formula below, one range is 8 rows - from 2 to 9 - but the other range is a whole column, not 8 rows.
=SUMIFS(INDEX(Sheet2!$B$2:$E$9,0,MATCH($J$3,Sheet2!$B$1:$E$1,0)),Sheet2!$A:$A,$H$5)
 
Upvote 0
The problem is that you have significantly changed the formula when changing from one to the other.


=SUMIFS(INDEX($B$4:$E$11,0,MATCH($J$3,$B$3:$E$3,0)),$A$4:$A$11,$H5)
In the formula above you have (correctly) made the two ranges the same size, 8 rows - from 4 to 11.

In the formula below, one range is 8 rows - from 2 to 9 - but the other range is a whole column, not 8 rows.
=SUMIFS(INDEX(Sheet2!$B$2:$E$9,0,MATCH($J$3,Sheet2!$B$1:$E$1,0)),Sheet2!$A:$A,$H$5)

Thanks peter.:(
 
Upvote 0
Barry, is there a way to ignore Error values with this same formula? All of the above solutions recommended are great, but none so far address the issue of errors in the sum range.
 
Upvote 0
Barry, is there a way to ignore Error values with this same formula? All of the above solutions recommended are great, but none so far address the issue of errors in the sum range.
I haven't used the sheet name here but I'm sure you are modifying anyway, so try something like this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

{=SUM(IF(A8:A235=B8,IF(J7=F2:BI2,IF(ISNUMBER(F8:BI235),F8:BI235))))}
 
Upvote 0
Peter, it worked great. You sir, are brilliant! I can't thank you enough, you've saved my day! All the best.
 
Upvote 0
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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