Calculation based on two different values in adjacent cells

[network_engineer]

Hello all,

This is related to an earlier post here; however, I now wish more changes, and wondered if should create a new post...!

The previous one was solved by using:
=(SUMPRODUCT(--(D27:D65536="High");--(G27:G65536="Non-Veg");((E27:E65536)))); I tried modifying it but failed to get the output that I wanted.

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; however, one multiple values from one of the columns should be taken into account? E.g.

E.g.



D27="High"; G27="Non-Veg"; E27=2
D28="Low"; G28="Non-Veg"; E28=2
D29="High"; G29="Veg"; E29=2
D30="Per"; G30="Non-Veg"; E30=2
D31="High"; G31="Non-Veg"; E31=2
D32="Per"; G32="Veg"; E32=2
__________________________________E32=6

So, in other words, the values in rows 28, 29 & 32 are ignored, because of columns D and G.

Thanks a lot for any help.

Kind regards.
Ben

 

[arthurbr]

Perhaps =(SUMPRODUCT(--(D27:D65536="High");--(G27:G65536="Non-Veg");(D27:D65536<>"low");(G27:G65536<>"veg");((E27:E65536))))

 

[venkat1926]

try this formula

=SUMPRODUCT((D27:D32<>"low")*(G27:G32<>"veg")*(H27:H32))

 

[network_engineer]

Hi all,

Nope, it did not work, it gave me an error:

#VALUE!

Any ideas?

Kind regards.

 

[T. Valko]

Hello all,

This is related to an earlier post here; however, I now wish more changes, and wondered if should create a new post...!

The previous one was solved by using:
=(SUMPRODUCT(--(D27:D65536="High");--(G27:G65536="Non-Veg");((E27:E65536)))); I tried modifying it but failed to get the output that I wanted.

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; however, one multiple values from one of the columns should be taken into account? E.g.

E.g.



D27="High"; G27="Non-Veg"; E27=2
D28="Low"; G28="Non-Veg"; E28=2
D29="High"; G29="Veg"; E29=2
D30="Per"; G30="Non-Veg"; E30=2
D31="High"; G31="Non-Veg"; E31=2
D32="Per"; G32="Veg"; E32=2
__________________________________E32=6

So, in other words, the values in rows 28, 29 & 32 are ignored, because of columns D and G.

Thanks a lot for any help.

Kind regards.
Ben

If I understand what you want, maybe this...

=SUMPRODUCT(SIGN((D27:D65536="High")+(G27:G65536="Non-Veg"));E27:E65536)

 

[network_engineer]

Hi!

Well, apologies, nope, it is not taking both values, i.e. let me try and explain this in a more simple manner.

I have cows, sheep, dogs, horses, and cats as values in Column A,
I have their respective counts in Column B
I have in column C counts, if the animal in Column A has any young ones.

So, e.g.

: Cow 10 2
: Sheep 5 1

I.e.
- I have Cows, 10 of them, of which 2 are calves
- I have Sheep, 5 of them, of which 1 is a lamb

Firstly: I want to calculate the milch cattle, then I need to take all values in Column A with either cows or sheets ONLY and ignore the others.
So, total 15.

Secondly: I want to calculate young ones of milch animals, so 3

Does that explain better?

Kind regards,
Ben

 

[T. Valko]

Hi!

Well, apologies, nope, it is not taking both values, i.e. let me try and explain this in a more simple manner.

I have cows, sheep, dogs, horses, and cats as values in Column A,
I have their respective counts in Column B
I have in column C counts, if the animal in Column A has any young ones.

So, e.g.

: Cow 10 2
: Sheep 5 1

I.e.
- I have Cows, 10 of them, of which 2 are calves
- I have Sheep, 5 of them, of which 1 is a lamb

Firstly: I want to calculate the milch cattle, then I need to take all values in Column A with either cows or sheets ONLY and ignore the others.
So, total 15.

Secondly: I want to calculate young ones of milch animals, so 3

Does that explain better?

Kind regards,
Ben

Ok, then it sounds like you need to include a column to identify milch animals (I had to look that up in the dictionary!)

Like this maybe:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 58px"><COL style="WIDTH: 58px"><COL style="WIDTH: 58px"><COL style="WIDTH: 58px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Cow</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">milch</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">10</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Sheep</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">milch</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD></TR></TBODY></TABLE>


Then formulas like this...

Count of all milch animals:

=SUMIF(B2:B3,"milch",C2:C3)

Count of youngsters of milch animals:

=SUMIF(B2:B3,"milch",D2:D3)