• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumif and indirect formula error

jrl1208

Member
Hi
I've combined a sumifand indirect formula but its only adding figures in one column instead of two
Can anyone correct my formula so that it adds two or more column .
Below is my formula:
=(SUMIF(INDIRECT("'"&$A3&"'!A:A"),MONTH(W$2),INDIRECT("'"&$A3&"'!AA:AB"))*Rates!$E$31)+(SUMIF(INDIRECT("'"&$A3&"'!A:A"),MONTH(W$2),INDIRECT("'"&$A3&"'!L:V"))*Rates!$E$30)+(SUMIF(INDIRECT("'"&$A3&"'!A:A"),MONTH(W$2),INDIRECT("'"&$A3&"'!K:K")))+(SUMIF(INDIRECT("'"&$A3&"'!A:A"),MONTH(W$2),INDIRECT("'"&$A3&"'!AC:AD"))*Rates!$E$32)


I ask the formula to sum data from column AA and AB but the formula only sums data in Column AA. Same with L:V and AC:AD.
 
Hi,

1) SUMPRODUCT:
You can try SUMPRODUCT to sum multiple columns.

See the first part:
=(SUMPRODUCT((INDIRECT("'"&$A3&"'!A2:A100")=MONTH(W$2))*(INDIRECT("'"&$A3&"'!AA2:AB100")))*$E$31)

Do the same for other and adjust the range accordingly.

2) SUMIF:
Or you can make a helper column say AC2 with simple sum =AA2+AB2 (copy down till end) and the replace your range with helper column, like:
=(SUMIF(INDIRECT("'"&$A3&"'!A:A"), MONTH(W$2), INDIRECT("'"&$A3&"'!AC:AC"))*Rates!$E$31)

Regards,
 
Back
Top