# Excel on Mac - Help with a formula

20 Reputation points
2024-08-21T01:40:06.74+00:00

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

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,835 questions

1. 2,756 Reputation points
2024-08-21T04:22:15.0466667+00:00

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.

2. 165 Reputation points
2024-08-21T08:01:41.24+00:00

Alternatively you may simplify Barry's formula to:

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