Calculation based on values in two adjacent cells.

[network_engineer]

Hello all,

Wondering if somebody here would help me with this:

How would I calculate the sum in a cell, if I want the value to be calculated ONLY if two values in the respective rows are matching?

E.g.
D17="High"; G17="Y"; E17=2
D18="High"; G18="N"; E18=2
D19="High"; G19="Y"; E19=2
D20="Low"; G20="N"; E20=2

__________________E21=4

So, in other words, the values in blue (E18 and E20) are ignored and not taken into calculation.

Thanks a lot for any help.

Kind regards.
Ben

 

[Aladin Akyurek]

Hi all,

It returns me a value of zero (0). Could somebody tell me what is wrong with this? (I changed a few values as I updated my workbook)

=SUMPRODUCT((D17:D65536="High");(G17:G65536="Non-Veg");(E17:E65536))

I also tried the same thing with SUMIFS, but I am getting an error:

=SUMIFS(E17:E65536;D17:D65536="High";G17:G65536="Non-Veg")

Kind regards,
Ben

Try to stipulate the conditions of a desired calculation though...

You need to modify the syntax in the expressions...

1] Just enter:

=SUMIFS(E17:E65536;D17:D65536;"High";G17:G65536;"Non-Veg")

2] Just enter:

=SUMPRODUCT(--(D17:D65536="High");--(G17:G65536="Non-Veg");(E17:E65536))

3] Also: Control+shift+enter...

=SUM(IF(D17:D65536="High";IF(G17:G65536="Non-Veg";E17:E65536)))

 

[network_engineer]

Hi all,

Thank you all, it worked with the SUMPRODUCT!


Kind regards,
Ben