Excel on Mac - Help with a formula

Question

Hi Microsoft Q&A Community,

I have a question about how to tweak an Excel formula that I'm sure someone will be able to answer.

The following formula has worked great: =SUMIFS('List of Transactions'!G3:G373,'List of Transactions'!K3:K373,"R",'List of Transactions'!G3:G373,">"&0)

However, I need to add a new functionality to the formula. Here's what I need to do, with the new functionality in bold.

• Sum column G in the List of Transactions tab IF
• There's an R in Column K
• OR there's an R in Column I
• And the number in Column G is greater than 0

Thanks in advance for your help!!

Answer 1

The SUMPRODUCT function can do what you want.

Consider the expression (K1="R")+(I1="R"). This can evaluate to 0, 1, or 2. If we add 1 to this and then perform integer division by 2, the result will always be 0 or 1. 0 if neither cell contain s an R and if either cell or both contains an R.

The desired formula is

=SUMPRODUCT(INT(((K3:K373="R")+(I3:I373="R")+1)/2)*(G3:G373>0)*G3:G373)

Add the values only if there is at least one R and the value is positive.

Answer 2

Alternatively you may simplify Barry's formula to:

=SUM(SIGN((I3:I373="R")+(K3:K373="R")*(G3:G373>0))*G3:G373)