CODEWITHSUNDEEP
excelif-statementnestedsumrange
user7182686
user7182686

Reputation:

Summing the result of nested IF statements in one cell

I've got a pretty complex conditional formula that works for each row of a column (sorry, no excel 2016 IFS) and I would like to get the sum of all instances in a range in one formula without having to make all the rows as a middle step.

Done this quite a bit with other stuff, but for some reason I'm stuck on this one.

The formula per cell is:

=IF((IF(IF(AND(ISNUMBER(Test_Samples!B2),Test_Samples!B2>0.1),Reference_Dataset!$H:$H,"")="N",Test_Samples!B2,0)<1),0,IF(IF(IF(AND(ISNUMBER(Test_Samples!B2),Test_Samples!B2>0.1),Reference_Dataset!$H:$H,"")="N",Test_Samples!B2,0)<2,1,IF(IF(IF(AND(ISNUMBER(Test_Samples!B2),Test_Samples!B2>0.1),Reference_Dataset!$H:$H,"")="N",Test_Samples!B2,0)<5,2,IF(IF(IF(AND(ISNUMBER(Test_Samples!B2),Test_Samples!B2>0.1),Reference_Dataset!$H:$H,"")="N",Test_Samples!B2,0)<13,3,IF(IF(IF(AND(ISNUMBER(Test_Samples!B2),Test_Samples!B2>0.1),Reference_Dataset!$H:$H,"")="N",Test_Samples!B2,0)<34,4,IF(IF(IF(AND(ISNUMBER(Test_Samples!B2),Test_Samples!B2>0.1),Reference_Dataset!$H:$H,"")="N",Test_Samples!B2,0)<91,5,IF(IF(IF(AND(ISNUMBER(Test_Samples!B2),Test_Samples!B2>0.1),Reference_Dataset!$H:$H,"")="N",Test_Samples!B2,0)<245,6,IF(IF(IF(AND(ISNUMBER(Test_Samples!B2),Test_Samples!B2>0.1),Reference_Dataset!$H:$H,"")="N",Test_Samples!B2,0)<666,7,))))))))

I would like to transform it to a formula that sums everything from the range B:B (or B2:B499) in one go.

I tried some SUM and SUMIF(S) stuff and changing B2 to B:B. That doesn't seem to work.

Oh, if someone has a tip to reduce the nested IF formula to something more readable, that's welcome as well. The idea of the formula is to transform counts to classes.

The datasets that are referred to look like this:

Test_Samples:

enter image description here

Reference_Dataset:

enter image description here

The If statements make up a classification as follows:

0= 0

1= 1

2= 2-4

3= 5-12

4= 13-33

5= 34-90

6= 91-244

7= 245-665

8= 666+

Here you see a count of 2 in "Test_samples", and it is labelled "N" in "Reference_dataset", so the result classifies it as "2" (to avoid confusion: if the count was 5 it would be labelled "3" according to the class criteria). Say if there are 5 instances with result "2" in the range B2:B499, the sum should be 10.

Upvotes: 0

Views: 528

Answers (1)

tigeravatar
tigeravatar

Reputation: 26670

Create a lookup table like this for your "result value < X" to summarize the If statements into a single lookup table:

Test_Samples B Value
0
1
2
5
13
34
91
245
666

In this example, i've put that on the same worksheet that the formula is placed, in cells A1:A10 (header in A1, so data values in A2:A10). Then you can simplify your formula and make it reference the range of your data like this:

=SUM(MATCH(IF(ISNUMBER(Test_Samples!$B$2:$B$499)*(Reference_Dataset!$H$2:$H$499="N"),Test_Samples!$B$2:$B$499,0),$A$2:$A$10)-1)

Note that this is an array formula and as such must be confirmed with CtrlShiftEnter (instead of just Enter).

Upvotes: 1

Related Questions