SUMIFS with multiple sumrange

[mmille10]

Hi,

I am looking for the best way to make a formula to sum two different columns. I have my sumrange in columun G and T. I want it to use the number in G if the cell in T is blank, if it is not blank use the value in T in the sum.


Thanks in advance for your help.

[TABLE="width: 1287"]
<colgroup><col><col><col span="2"><col><col><col span="2"><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

Here is one way:

=SUM(T:T) + SUMPRODUCT(--(T:T=""),--(G:G))

 

[mmille10]

Are you able to explain the dashes please? If i don't understand it i am less likely to remember.

Thanks for the quick response!

 

[Joe4]

It simply coerces each value to return numbers (in case it returned text).
It may make more sense written like this (which often works too):

=SUM(T:T) + SUMPRODUCT((T:T="")*(G:G))

The other way just reduces the chance of issues or unexpected results.

See here for further explanation and links: https://www.mrexcel.com/forum/excel-questions/660559-what-does-mean-sumproduct-formula.html