Multiple criteria for SUMIFS

[Chicken6]

Hello all!

I am attempting to use SUMIFS to sum a column. However all the examples I have Googled have single criterion.

I have included a sample of my dataset below. Each subject has 9 rows (for various reasons) and I have included two of the subjects (300 and 301).

I need to sum the CLASS_wtmean (a weighted mean calculated from the CLASS column) for each subject. But only three of the values. Specifically the CLASS_sum = (Order = 1 and dataset = "all") + (Order = 2 and dataset = "all") + (Order = 3 and dataset = "prefail").

In the example below the answer for the CLASS_sum for subject 300 would be 0.661 (0.249 + 0.322 + 0.090).
For subject 301 it would be 0.899. I have repeated the answer for each row of a subject as I use R to subset various rows afterwards. I want to create a formula for the CLASS_sum column that I can drag down for the rest of the dataset!

I have tried using SUMIFS but came to a grinding halt when I realised I need to have more than one column criteria.
<code style="font-family: monospace, serif;">SUMIFS(sumrange,criteriarange1,criteria1[,criteriarange2,criteria2[,...]])</code>

=SUMIFS(AB236:AB244, D236:D244=1 AND F236:F244="all",... Not sure where to go from here . I am not sure if you can use 'and' in a SUMIFS() formula.

My formula experience has been pretty much limited to SUM and rudimentary IF statements. Any clues would be greatly appreciated!




[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]subj[/TD]
[TD]Order[/TD]
[TD]Scenario[/TD]
[TD]dataset[/TD]
[TD]CLASS[/TD]
[TD]CLASS_wtmean[/TD]
[TD]CLASS_sum[/TD]
[/TR]
[TR]
[TD]236[/TD]
[TD]300[/TD]
[TD]3[/TD]
[TD]Co[/TD]
[TD]all[/TD]
[TD]0.670[/TD]
[TD]0.096[/TD]
[TD]0.661[/TD]
[/TR]
[TR]
[TD]237[/TD]
[TD]300[/TD]
[TD]3[/TD]
[TD]Co[/TD]
[TD]postfail[/TD]
[TD]0.690[/TD]
[TD]NA[/TD]
[TD]0.661[/TD]
[/TR]
[TR]
[TD]238[/TD]
[TD]300[/TD]
[TD]3[/TD]
[TD]Co[/TD]
[TD]prefail[/TD]
[TD]0.630[/TD]
[TD]0.090[/TD]
[TD]0.661[/TD]
[/TR]
[TR]
[TD]239[/TD]
[TD]300[/TD]
[TD]1[/TD]
[TD]Ex[/TD]
[TD]all[/TD]
[TD]0.580[/TD]
[TD]0.249[/TD]
[TD]0.661[/TD]
[/TR]
[TR]
[TD]240[/TD]
[TD]300[/TD]
[TD]1[/TD]
[TD]Ex[/TD]
[TD]postfail[/TD]
[TD]0.500[/TD]
[TD]NA[/TD]
[TD]0.661[/TD]
[/TR]
[TR]
[TD]241[/TD]
[TD]300[/TD]
[TD]1[/TD]
[TD]Ex[/TD]
[TD]prefail[/TD]
[TD]0.750[/TD]
[TD]0.322[/TD]
[TD]0.661[/TD]
[/TR]
[TR]
[TD]242[/TD]
[TD]300[/TD]
[TD]2[/TD]
[TD]Re[/TD]
[TD]all[/TD]
[TD]0.750[/TD]
[TD]0.322[/TD]
[TD]0.661[/TD]
[/TR]
[TR]
[TD]243[/TD]
[TD]300[/TD]
[TD]2[/TD]
[TD]Re[/TD]
[TD]postfail[/TD]
[TD]0.750[/TD]
[TD]NA[/TD]
[TD]0.661[/TD]
[/TR]
[TR]
[TD]244[/TD]
[TD]300[/TD]
[TD]2[/TD]
[TD]Re[/TD]
[TD]prefail[/TD]
[TD]0.750[/TD]
[TD]0.322[/TD]
[TD]0.661[/TD]
[/TR]
[TR]
[TD]245[/TD]
[TD]301[/TD]
[TD]2[/TD]
[TD]Co[/TD]
[TD]all[/TD]
[TD]0.920[/TD]
[TD]0.395[/TD]
[TD]0.899[/TD]
[/TR]
[TR]
[TD]246[/TD]
[TD]301[/TD]
[TD]2[/TD]
[TD]Co[/TD]
[TD]postfail[/TD]
[TD]0.880[/TD]
[TD]NA[/TD]
[TD]0.899[/TD]
[/TR]
[TR]
[TD]247[/TD]
[TD]301[/TD]
[TD]2[/TD]
[TD]Co[/TD]
[TD]prefail[/TD]
[TD]1.000[/TD]
[TD]0.429[/TD]
[TD]0.899[/TD]
[/TR]
[TR]
[TD]248[/TD]
[TD]301[/TD]
[TD]3[/TD]
[TD]Ex[/TD]
[TD]all[/TD]
[TD]0.920[/TD]
[TD]0.132[/TD]
[TD]0.899[/TD]
[/TR]
[TR]
[TD]249[/TD]
[TD]301[/TD]
[TD]3[/TD]
[TD]Ex[/TD]
[TD]postfail[/TD]
[TD]0.940[/TD]
[TD]NA[/TD]
[TD]0.899[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]301[/TD]
[TD]3[/TD]
[TD]Ex[/TD]
[TD]prefail[/TD]
[TD]0.880[/TD]
[TD]0.126[/TD]
[TD]0.899[/TD]
[/TR]
[TR]
[TD]251[/TD]
[TD]301[/TD]
[TD]1[/TD]
[TD]Re[/TD]
[TD]all[/TD]
[TD]0.880[/TD]
[TD]0.378[/TD]
[TD]0.899[/TD]
[/TR]
[TR]
[TD]252[/TD]
[TD]301[/TD]
[TD]1[/TD]
[TD]Re[/TD]
[TD]postfail[/TD]
[TD]0.810[/TD]
[TD]NA[/TD]
[TD]0.899[/TD]
[/TR]
[TR]
[TD]253[/TD]
[TD]301[/TD]
[TD]1[/TD]
[TD]Re[/TD]
[TD]prefail[/TD]
[TD]1.000[/TD]
[TD]0.429[/TD]
[TD]0.899[/TD]
[/TR]
[TR]
[TD]254[/TD]
[TD]302[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

This should work for you:

=SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,1,$D$2:$D$1000,"all")+SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,2,$D$2:$D$1000,"all")+SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,3,$D$2:$D$1000,"prefail")

My original version only printed the total on the first line of each subject. Assuming your data starts in line 2:

=IF(MATCH(A236,$A$2:$A$1000,0)+ROW($A$2)-1<>ROW(A236),"",SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,1,$D$2:$D$1000,"all")+SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,2,$D$2:$D$1000,"all")+SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,3,$D$2:$D$1000,"prefail"))

Note that both of those formulas reference A236, so paste the formula in AC236, hit enter, then copy it to the rest of the column. They also have a maximum range of 1000, which you'll need to verify.

 

[bosco_yip]

Try,

=SUM(SUMIFS(AB236:AB254,A236:A254,300,D236:D254,{1,2},F236:F254,"all"))+SUMIFS(AB236:AB254,A236:A254,300,D236:D254,3,F236:F254,"prefail")

Remark :
The above formula subject in column A use 300, the result return 0.661. And if the subject in column A change to 301 it would be 0.899


Regards

 

[Chicken6]

This should work for you:

=SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,1,$D$2:$D$1000,"all")+SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,2,$D$2:$D$1000,"all")+SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,3,$D$2:$D$1000,"prefail")

My original version only printed the total on the first line of each subject. Assuming your data starts in line 2:

=IF(MATCH(A236,$A$2:$A$1000,0)+ROW($A$2)-1<>ROW(A236),"",SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,1,$D$2:$D$1000,"all")+SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,2,$D$2:$D$1000,"all")+SUMIFS($F$2:$F$1000,$A$2:$A$1000,A236,$B$2:$B$1000,3,$D$2:$D$1000,"prefail"))

Note that both of those formulas reference A236, so paste the formula in AC236, hit enter, then copy it to the rest of the column. They also have a maximum range of 1000, which you'll need to verify.

Thank you very very much Eric! This worked I changed the cell range to just the one subject rather than the whole lot, and made the cells underneath equal that cell (I changed the first criterion range to AB rather than F columns too):

=SUMIFS(AB236:AB244, D236:D244,1,F236:F244,"all") + SUMIFS(AB236:AB244, D236:D244,2,F236:F244,"all")+SUMIFS(AB236:AB244,D236:D244,3,F236:F244,"prefail")

I have tested it with manual calculations and all good. You have saved me days of pain. Thank you!

 

[Chicken6]

this also worked! I have seen the curly brackets used but wasn't sure of how they could be applied. Thanks BoscoYip.

 

[bosco_yip]

this also worked! I have seen the curly brackets used but wasn't sure of how they could be applied. Thanks BoscoYip.

The constant array {1,2} in the formula :

=SUM(SUMIFS(AB236:AB254,A236:A254,300,D236:D254, {1,2},F236:F254,"all")) is equal to (subject = 3 + Order = 1 and dataset = "all") + (subject = 3 + Order = 2 and dataset = "all")

 

[Eric W]

Happy to have helped.