IF Function, using values from (2) different columns

Question

Hello,

I have (4) different criteria that are possible data in column G (example: red, blue, yellow, green).  I have dollar amounts in column C.  I want to do a formula as follows:

If the values in each cell of column G are 'yellow' or 'green' (either/or), add up the corresponding row dollar amount from column C.

How do I do that?

Answer 1

Prefer Murray's formula ... you may use SUMIF instead:

=SUM(SUMIF(G2:G10,{"green","yellow"},C2:C10))

Regards,

Amit Tandon

Answer 2

Hi,

Try this

=SUMPRODUCT(($C$2:$C$100)*(($G$2:$G$100="Yellow")+($G$2:$G$100="Green")))

Hope this helps.

Answer 3

One way:

=SUM(SUMIFS(C4:C11,G4:G11,{"Green","Yellow"}))

Answer 4

Hi Amanda,

You could apply an IF Function to each row:

=IF(OR(G2="Yellow",G2="Green"),C2,0)

Then sum the column containing those formulas.

However, if you want all of that to be calculated in a single cell you would need:

{=SUM(IF(G2:G6="Yellow",C2:C6,0),IF(G2:G6="Green",C2:C6,0))}

This formula assumes all the data is in rows 2 through 6.  It is an array formula, so you don't type the { } curly brackets.  Enter the formula, then press Ctrl + Shift + Enter to input the formula.