The other alternative for SUMIF formula

[hudan]

the disadvantage of sumif is that if we use a different source file, then when we close the current file, the results of our formula will change to #value .
Do you know what the other alternatives besides sumif

 

[Twollaston]

the disadvantage of sumif is that if we use a different source file, then when we close the current file, the results of our formula will change to #value .
Do you know what the other alternatives besides sumif

Use SUMPRODUCT(--(A:A="Criteria"), SumRange)
ranges must be the same size

you can set whatever criteria you want so instead of A:A="Criteria" you could maybe want, if it's for customer john in column B
B:B="John" or if column c is >100, C:C>100, etc

and for your sum range you can just grab the same as the sum range for a sum if

 

[Twollaston]

Use SUMPRODUCT(--(A:A="Criteria"), SumRange)
ranges must be the same size

you can set whatever criteria you want so instead of A:A="Criteria" you could maybe want, if it's for customer john in column B
B:B="John" or if column c is >100, C:C>100, etc

and for your sum range you can just grab the same as the sum range for a sum if

you can also have multiple criteria for instance we want to sum if when column A = "John" and Column B = "June" and we want to sum the number in column C

AND Criteria, both conditions need to be true
SUMPRODUCT((A:A = "John")*(B:B = "June"), (C:C)) or SUMPRODUCT(--(A:A = "John"),--(B:B = "June"), (C:C))

either condition is true
SUMPRODUCT((A:A = "John")+(A:A = "James"), (C:C))

 

[hudan]

Yess your formula was successful, i just tried that, thanks a lot mate.

 

[hudan]

but Sumproduct formula will cause the computer to work slow if it works with many rows.

 

[Twollaston]

Sorry friend I'm not sure of another formula that will work with a closed source file, someone else on here might know though