Excel Summation Function

[Subhi]

Dear Gents,

As shown in the below table I have 2 categories ( Near and Far ), I want the summation of the quantities for all IDs in the Near column which are facing the same ID in the Far column.

I applied the formula [ =SUMIFS(C2:C7,B2:B7,E2) ] for M ID and it works, but when apply it for Z ID it gives me 5 which isn’t correct and it has to add the summation result for M ID ( 5 + 7 = 12 ).

I need your support to provide me with a formula which gives the summation of Z ID for this scenario.

 

[awoohaw]

why do you need to consider the Near column if you are just checking for M and Z in the Far column?

 

[Subhi]

Dears,

The below diagram descrips the scenario.

As I explained I applied the upper formula [ =SUMIFS(C2:C7,B2:B7,E2) ] and worked for M ID and didn't worked for Z ID.

Is there any formula which gives the total sumation for the Z ID, it has to be ( Z = B+E+M and M = A+C+D ).

 

[Subhi]

Dear Gents,

Your kind support and feedback here are more appreciated.

 

[awoohaw]

try this, it is probably on specific to your exact example above though:

Book1
	A	B	C	D	E	F
1	Near	Far	Quantity of Near		Far	Total Quantity
2	A	M	2		M	12
3	B	Z	4		Z	5
4	C	M	3
5	M	Z
6	D	M	2
7	E	Z	1
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=SUM((--(A2:A7=$E2))*SUM((--(B2:B7="Z"))*(C2:C7)))+ SUM((--(B2:B7=$E2))*(C2:C7))

 

[Subhi]

I applied your formula but it gave me wrong answers.

As per attached diagram, the summation of M has to be equal 7 and the summation of Z has to be equal 12.

Is there another formula should give the correct answers ?

 

[awoohaw]

I was under the impression you wanted the result in the image in post #1. Please post an excel mini page with your expectation.

But, to start, why don't you try to reverse the Letters in the solution I posted?

 

[Subhi]

The image in Post#1 and the image #2 are the same but I presented them in two ways ( excel sheet and diagram).

All what I need is a formula gives me the total summation for Z ID from all IDs as shown in the diagram.

Jeddah Projects-Update-03-05-2023.xlsx
	A	B	C	D	E	F	G	H
1	Near	Far	Quantity of Near		Far	Total Quantity		expectated Value
2	A	M	2		M	2		7
3	B	Z	4		Z	4		12
4	C	M	3
5	M	Z
6	D	M	2
7	E	Z	1
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=SUM((--(A2:A7=$E2))*SUM((--(B2:B7="Z"))*(C2:C7)))+ SUM((--(B2:B7=$E2))*(C2:C7))

 

[Subhi]

Dear Gents,

Any Good news ?

 

[Subhi]

Dear Gents,

I'm still looking for your support !!