Using SUMIF to Match Multiple Criteria

[xpl0it]

Hello, MrExcel Forum,

I would like to be able to accomplish the following.

If cells C13:C28 and F13:F28 = 1, 2, 3, 4, 5, and D13:D28 and F13:F28 = "test" then 1 for true.


The issue I'm having is trying to utilize multiple cell ranges. Here is a one of the many formula's I've been working at.


=SUM(IF($B$13:$B$28={1,2,3,4,5,6,7,8,9,10,11,12},IF($C$13:$C$28="New Mod",1,0),0))+SUM(IF($B$42:$B$54={1,2,3,4,5,6,7,8,9,10,11,12},IF($C$42:$C$54="New Mod",1,0),0)+SUM(IF($F$5:$F$28={1,2,3,4,5,6,7,8,9,10,11,12},IF($G$5:$G$28="New Mod",1,0),0)+SUM(IF($F$42:$F$53={1,2,3,4,5,6,7,8,9,10,11,12},IF($G$42:$G$53="New Mod",1,0),0)+SUM(IF($J$7:$J$28={1,2,3,4,5,6,7,8,9,10,11,12},IF($K$7:$K$28="New Mod",1,0),0)+SUM(IF($J$42:$J$53={1,2,3,4,5,6,7,8,9,10,11,12},IF($K$42:$K$53="New Mod",1,0),0)+SUM(IF($N$9:$N$33={1,2,3,4,5,6,7,8,9,10,11,12},IF($O$9:$O$33="New Mod",1,0),0)))))))
********>(function(){ function hasHandler(e) { return(e && e.toString().indexOf("alert") != -1); } if(hasHandler(window.oncontextmenu) || hasHandler(document.oncontextmenu)) window.oncontextmenu = document.oncontextmenu = null; if(hasHandler(window.onmousedown) || hasHandler(document.onmousedown)) window.onmousedown = document.onmousedown = null; if(hasHandler(window.onmouseup) || hasHandler(document.onmouseup)) window.onmouseup = document.onmouseup = null;})();*********>

 

[Aladin Akyurek]

Hello, MrExcel Forum,

I would like to be able to accomplish the following.

If cells C13:C28 and F13:F28 = 1, 2, 3, 4, 5, and D13:D28 and F13:F28 = "test" then 1 for true.


The issue I'm having is trying to utilize multiple cell ranges. Here is a one of the many formula's I've been working at.


=SUM(IF($B$13:$B$28={1,2,3,4,5,6,7,8,9,10,11,12},IF($C$13:$C$28="New Mod",1,0),0))+SUM(IF($B$42:$B$54={1,2,3,4,5,6,7,8,9,10,11,12},IF($C$42:$C$54="New Mod",1,0),0)+SUM(IF($F$5:$F$28={1,2,3,4,5,6,7,8,9,10,11,12},IF($G$5:$G$28="New Mod",1,0),0)+SUM(IF($F$42:$F$53={1,2,3,4,5,6,7,8,9,10,11,12},IF($G$42:$G$53="New Mod",1,0),0)+SUM(IF($J$7:$J$28={1,2,3,4,5,6,7,8,9,10,11,12},IF($K$7:$K$28="New Mod",1,0),0)+SUM(IF($J$42:$J$53={1,2,3,4,5,6,7,8,9,10,11,12},IF($K$42:$K$53="New Mod",1,0),0)+SUM(IF($N$9:$N$33={1,2,3,4,5,6,7,8,9,10,11,12},IF($O$9:$O$33="New Mod",1,0),0)))))))

...

Define Seq by means of insert|Name|Define or Formulas|Name Manager as referring to:

={1,2,3,4,5,6,7,8,9,10,11,12}

What follows covers the first two SUM's...

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(CHOOSE({1,2},B13:B28,B42:B54),Seq,0)),
    IF(CHOOSE({1,2},C13:C28,C42:C54)="New Mod",1)))

Do the same with the ranges in F:G and K:N but separately, all in cells of their own. Then simply sum these partial result cells.

 

[xpl0it]

@Aladin Akyurek


Thank you for taking the time to assist me! I don't think I did a very good job at explaining exactly what I was attempting to accomplish. I've decided to include a public worksheet below to give a visual to what I'm trying to describe.


I have 4 managers that divide up over 50 departments. The departments they over see are not sequential. I would like to calculate how many times the department appears in the columns under dept. and the inventory column states New Mod. I am trying to separate this out by manager calculated in their own respective cells.


I hope that being able to visualize what I'm trying to accomplish I won't waste to much of your time trying to explain myself.


http://sheet.zoho.com/public/xpl0it/backroom-pallet-inventory-4

 

[xpl0it]

Here is much clearer explanation of what I'd like to accomplish.

First take a look at the public worksheet I've posted.
http://sheet.zoho.com/public/xpl0it/...et-inventory-4

Scroll down until you see Manager 1, Manager 2, Manager 3, Manager 4. If you notice each one of these managers has numbers listed below. Those numbers are the department numbers that each manager is responsible for.

Now, I'd like to calculate how many times each Manager's departments are entered into ANY column that is under DEPT. and NEW MOD is entered under ANY inventory column. Both conditions have to be met in order for the calculation to be completed. IF the condition is true you will be adding a 1 to the corresponding managers cell.

Basically if I enter a department that is under Manager 1, and the inventory column is equal to New Mod, add 1 to a cell under Manager 1. I would like to do this for all four managers.

Hopefully I wasn't so confusing this time around.

Thanks Again
********>(function(){ function hasHandler(e) { return(e && e.toString().indexOf("alert") != -1); } if(hasHandler(window.oncontextmenu) || hasHandler(document.oncontextmenu)) window.oncontextmenu = document.oncontextmenu = null; if(hasHandler(window.onmousedown) || hasHandler(document.onmousedown)) window.onmousedown = document.onmousedown = null; if(hasHandler(window.onmouseup) || hasHandler(document.onmouseup)) window.onmouseup = document.onmouseup = null;})();*********>