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,>

 

[Andrew Poulsom]

Where is the sum criterion in that formula? Which range(s) do you want to use for the criteria, what's the criteria and which range(s) do you want to sum?

 

[phelps22]

Where is the sum criterion in that formula? Which range(s) do you want to use for the criteria, what's the criteria and which range(s) do you want to sum?

Size will refer to Cost!$H$3 (manual input)

Range is:
Size > 40, use 'Worksheet1'!AB:AB
<size<=20, use="" 'worksheet1'!y:y
<size<=20, use="" 'worksheet1'!y:y
Size greater than 30 but less than 40, use 'Worksheet1'!AA:AA
Size greater than 20 but less than 30, 'Worksheet1'!Z:Z
Size greater than 10 but less than 20, use 'Worksheet1'!Y:Y
Size greater than 5 but less than 10, 'Worksheet1'!X:X
</size<=20,></size<=20,>The rest, 'Worksheet1'!W:W

Before I asked to get the below functions on the range of size 5-30, but now I want to extend size range above.
=SUMIFS(IF(Cost!H3<=20,IF(Cost!H3>10,'Worksheet1'!Y:Y,IF(Cost!H3>5,'Worksheet1'!X:X,'Worksheet1'!W:W)),'Worksheet1'!Z:Z)

So how can I modify?

 

[Andrew Poulsom]

Sorry, I don't understand. That formula is syntactically incorrect - it can't be entered on a worksheet. The syntax for SUMIFS is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

So I'll repeat my question - which range(s) do you want to sum, which range(s) do you want to use for the criteria and what's the criteria?

 

[phelps22]

Sorry, I don't understand. That formula is syntactically incorrect - it can't be entered on a worksheet. The syntax for SUMIFS is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

So I'll repeat my question - which range(s) do you want to sum, which range(s) do you want to use for the criteria and what's the criteria?

Oh..sorry I just discovered.

Criteria is:

User will input size on cell Cost!H3

Size > 40, use 'Worksheet1'!AB:AB
<size<=20, use="" 'worksheet1'!y:y
<size<=20, use="" 'worksheet1'!y:y
Size greater than 30 but less than 40, use 'Worksheet1'!AA:AA
Size greater than 20 but less than 30, 'Worksheet1'!Z:Z
Size greater than 10 but less than 20, use 'Worksheet1'!Y:Y
Size greater than 5 but less than 10, 'Worksheet1'!X:X
</size<=20,></size<=20,>The rest, 'Worksheet1'!W:W

Sum_range: can I set it subject to Cost!H3 cell input?

 

[Andrew Poulsom]

You don't need SUMIFS there. Try:

=SUM(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))))))

 

[phelps22]

Oh..sorry I just discovered.

Criteria is:

User will input size on cell Cost!H3

Size > 40, use 'Worksheet1'!AB:AB
<size<=20, use="" 'worksheet1'!y:y
<size<=20, use="" 'worksheet1'!y:y
Size greater than 30 but less than 40, use 'Worksheet1'!AA:AA
Size greater than 20 but less than 30, 'Worksheet1'!Z:Z
Size greater than 10 but less than 20, use 'Worksheet1'!Y:Y
Size greater than 5 but less than 10, 'Worksheet1'!X:X
</size<=20,></size<=20,>The rest, 'Worksheet1'!W:W

Sum_range: can I set it subject to Cost!H3 cell input?

Let me show the whole function ...

=SUMIFS(IF(Cost!H3<=20,IF(Cost!H3>10,'Worksheet1'!Y:Y,IF(Cost!H3>5,'Worksheet1'!X:X,'Worksheet1'!W:W)),'Worksheet1'!Z:Z),'Worksheet1'!AD:AD,Cost!B2,'Worksheet1'!AB:AB,Cost!B3)

Actually I want to modify the highlighted in red with the above conditions

 

[phelps22]

You don't need SUMIFS there. Try:

=SUM(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))))))

o..can run like below? Because there are other 2 criteria ranges.
=SUMIFS(IF(Cost!H3<=20,IF(Cost!H3>10,'Worksheet1'!Y:Y,IF(Cost!H3>5,'Worksheet1'!X:X,'Worksheet1'!W:W)),'Worksheet1'!Z:Z),'Worksheet1'!AD:AD,Cost!B2,'Worksheet1'!AB:AB,Cost!B3)

 

[Andrew Poulsom]

You would have 'Worksheet'!AB:AB twice there:

=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!AD:AD,Cost!B2,Worksheet1!AB:AB,Cost!B3)

 

[phelps22]

You would have 'Worksheet'!AB:AB twice there:

=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!AD:AD,Cost!B2,Worksheet1!AB:AB,Cost!B3)

Thanks, it should be AD column
=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)

Then it will be valid?

 

[Andrew Poulsom]

Did you try it?