SUMIFS - using column & row criteria

[ErinP]

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.

 

[Peter_SSs]

Thanks for that clarification Barry.

 

[deccon]

That fixed it!!! Thanks Barry!!!

 

[Baskar]

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..

 

[Peter_SSs]

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)

 

[Baskar]

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.

 

[jworkman7]

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.

 

[Peter_SSs]

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))))}

 

[jworkman7]

Peter, it worked great. You sir, are brilliant! I can't thank you enough, you've saved my day! All the best.

 

[Peter_SSs]

Peter, it worked great. You sir, are brilliant! I can't thank you enough, you've saved my day! All the best.

You are very welcome.

 

[MARK858]

Duplicate post of
http://www.mrexcel.com/forum/excel-...le-sheets-multiple-criterias.html#post4720774
and
http://www.mrexcel.com/forum/excel-...ple-sheets-multiple-criteria.html#post4720775

@sunnyk, you are probably better off starting a new question yourself rather than a bunch of duplicate questions in other threads and also asking a Moderator to delete the duplicate posts.