Combining formulae

[Sicar]

I have two formulae is two separate columns and would like to combine them two reduce the number of columns, but I keep getting "too many arguments".

Formula to be extended
=IF($G10=0,"",(IFERROR(IF($F10="FSN",VLOOKUP($E10,'PPA part details'!$E:$AF,25,FALSE),"")*IF($H10=0,$G10,($G10*$H10)),"")))
Formula to add to the above
=IFERROR(IF(A10="","",IF(UPPER(A10)="S",SUMIF(B$6:B67, B10,T$6:T67), "")), "")

Both formulae work fine on their own in separate columns.

 

[Rhodie72]

I have two formulae is two separate columns and would like to combine them two reduce the number of columns, but I keep getting "too many arguments".

Formula to be extended
=IF($G10=0,"",(IFERROR(IF($F10="FSN",VLOOKUP($E10,'PPA part details'!$E:$AF,25,FALSE),"")*IF($H10=0,$G10,($G10*$H10)),"")))
Formula to add to the above
=IFERROR(IF(A10="","",IF(UPPER(A10)="S",SUMIF(B$6:B67, B10,T$6:T67), "")), "")

Both formulae work fine on their own in separate columns.

So what do you expect to happen? What do want it to do?

 

[Rhodie72]

To combine the two formulas into a single formula while keeping both logic conditions, you need to carefully structure the IF statements and ensure that the arguments are placed correctly. The error you're getting ("too many arguments") is likely due to the fact that you may be placing too many arguments inside an IF function or mismatching parentheses.

Here’s how you can combine the two formulas:

=IF($G10=0,"",IFERROR(IF($F10="FSN",VLOOKUP($E10,'PPA part details'!$E:$AF,25,FALSE),"")*IF($H10=0,$G10,($G10*$H10)), "")+IFERROR(IF(A10="","",IF(UPPER(A10)="S",SUMIF(B$6:B67, B10,T$6:T67), "")), ""))

If you'd like to handle specific conditions (like if the first part returns an empty string but the second one should still execute), you can nest the formulas further or replace the + with a conditional operator (IF).

 

[Sicar]

To combine the two formulas into a single formula while keeping both logic conditions, you need to carefully structure the IF statements and ensure that the arguments are placed correctly. The error you're getting ("too many arguments") is likely due to the fact that you may be placing too many arguments inside an IF function or mismatching parentheses.

Here’s how you can combine the two formulas:

=IF($G10=0,"",IFERROR(IF($F10="FSN",VLOOKUP($E10,'PPA part details'!$E:$AF,25,FALSE),"")*IF($H10=0,$G10,($G10*$H10)), "")+IFERROR(IF(A10="","",IF(UPPER(A10)="S",SUMIF(B$6:B67, B10,T$6:T67), "")), ""))

If you'd like to handle specific conditions (like if the first part returns an empty string but the second one should still execute), you can nest the formulas further or replace the + with a conditional operator (IF).

Thank you, just pasted this in to the cell I wanted and it didn't return anything.

I have attached a screen shot of the spreadsheet.

What I want to happen is this.

If A10 is "S" then T10 needs to use the formula =IFERROR(IF(A10="","",IF(UPPER(A10)="S",SUMIF(B$6:B67, B10,T$6:T67), "")), ""), but if it isnt "S" then it needs to use the formula =IF($G10=0,"",(IFERROR(IF($F10="FSN",VLOOKUP($E10,'PPA part details'!$E:$AF,25,FALSE),"")*IF($H10=0,$G10,($G10*$H10)),"")))

I hope this makes sense.

 

[Rhodie72]

Thank you, just pasted this in to the cell I wanted and it didn't return anything.

I have attached a screen shot of the spreadsheet.

What I want to happen is this.

If A10 is "S" then T10 needs to use the formula =IFERROR(IF(A10="","",IF(UPPER(A10)="S",SUMIF(B$6:B67, B10,T$6:T67), "")), ""), but if it isnt "S" then it needs to use the formula =IF($G10=0,"",(IFERROR(IF($F10="FSN",VLOOKUP($E10,'PPA part details'!$E:$AF,25,FALSE),"")*IF($H10=0,$G10,($G10*$H10)),"")))

I hope this makes sense.

To accomplish the goal you outlined, you need a formula that will dynamically adjust based on the value in cell A10. Since you want to handle this directly within Excel (without using VBA), you can use an IF statement that checks if A10 is "S" and then applies the appropriate formula.


Here’s the formula you can use in cell T10:

=IF(UPPER(A10)="S", IFERROR(IF(A10="","",SUMIF(B$6:B67, B10, T$6:T67)), ""), IF($G10=0, "", IFERROR(IF($F10="FSN", VLOOKUP($E10, 'PPA part details'!$E:$AF, 25, FALSE), "") * IF($H10=0, $G10, ($G10*$H10)), "")))

Often errors arise due to case sensitivity so this is why using UPPER is in the formula.

 

[Sicar]

Thank you, I will give that a try

 

[Sicar]

So, I have tried the formula and it works as long as column A has an "S" in it. if it is blank I get #VALUE. Also the number is a factor of 100 out. e.g. the result is 232.00 when it should be 2.32 in my test example.