SUMIFS with IF function on sum_range

[phelps22]

I have a set of SUMIF formula like:

=SUMIFS('Worksheet1'!W:W,'Worksheet1'!AD:AD,Cost!B2,'Worksheet1'!AB:AB,Cost!B3)

But now want to insert criterias on sum_range, let's say

Size > 20, use 'Worksheet1'!Z:Z
<size<=20, use="" 'worksheet1'!y:y
<size<=20, use="" 'worksheet1'!y:y
Size greater than 10 but less than 20, use 'Worksheet1'!Y:Y
the rest, 'Worksheet1'!W:W

How can I modify it to incorporate above criterias on sum_range? As just insert IF functions, too many arguments encountered.</size<=20,></size<=20,>

 

[phelps22]

Did you try it?

Thanks, it's work. But when I input the value of cost!H3 <5, the function is not work, i.e cannot reach column W value. Any modification?

 

[Andrew Poulsom]

Sorry, I don't understand. There is no less than 5 in the formula I posted.

 

[phelps22]

Sorry, I don't understand. There is no less than 5 in the formula I posted.

But can it look up value if less than 5?

 

[Andrew Poulsom]

What did you try? Column W is used if none of the other criteria is met.

 

[phelps22]

What did you try? Column W is used if none of the other criteria is met.

As per you said, Column W should be used if none of the other criteria is met (i.e. the value is less than 5). But it shows error..

For example, my formula is set like the below table:

Column		If input value (at separate cell) is greater than, it will look up the column:
AB		>40
AA		>30
Z		>20
Y		>10
X		>5
W		>0

<tbody>
</tbody>

Now the question is that it cannot look up the value at column W if I input the value <5 (i.e. 3)

=SUMIFS(IF(Cost!H3 > 40,Worksheet1!AB:AB,IF(Cost!H3 > 30,Worksheet1!AA:AA,IF(Cost!H3 > 20,Worksheet1!Z:Z,IF(Cost!H3 > 10,Worksheet1!Y:Y,IF(Cost!H3 > 5,Worksheet1!X:X,Worksheet1!W:W))))),Worksheet1!AF:AF,Cost!B2,Worksheet1!AD:AD,Cost!B3)

 

[Andrew Poulsom]

Your formula sums column W if Cost!H3 is less than 5. I just tested it. What do you mean by 'shows error'?

 

[phelps22]

Your formula sums column W if Cost!H3 is less than 5. I just tested it. What do you mean by 'shows error'?

But it cannot show sums column W if less than 5..showing "#N/A"

 

[Andrew Poulsom]

SUMIFS will return #N/A the column to be summed contains one or more cells with that error.

 

[phelps22]

SUMIFS will return #N/A the column to be summed contains one or more cells with that error.

Sorry, dont understand it..

 

[Andrew Poulsom]

Check that column W doesn't contain any #N/A errors.