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