SUMIFS Formula

[mj_1986]

Hi,
I am trying to SUMIFS based on 2 criteria - number and a description but the sumif wont differentiate between the 8502 and the 850200 codes and is therefore suming both rather than just the one that i want.

I am using this formula but something is missing. How do i fix this so the get 600 for 1111.8502 & 600 for 1111.850200

Thank you!

 

[DanteAmor]

Use the following, but you will have to limit the range

varios 03ene2024.xlsm
	A	B	C	D	E	F	G
1
2	1111.8502	APPLES	600		1111.8502	APPLES	100
3	1111.850200	APPLES	800		1111.8502	APPLES	200
4					1111.8502	APPLES	300
5					1111.85020	ORANGES	400
6					1111.85020	BANANAS	500
7					1111.850200	APPLES	800
Hoja2

Cell Formulas
Range	Formula
C2:C3	C2	=SUMPRODUCT(($E$2:$E$100=A2)*($F$2:$F$100=B2)*($G$2:$G$100))

 

[Dave Patton]

Please edit your profile to show the version of Excel that you use.
I included a formula that requires a fairly recent version of Excel.
I assumed columns A and E are text

Sumifs.xlsm
	A	B	C	D	E	F	G	H	I
1	Text				Text
2	1111.8502	APPLES	600		1111.8502	APPLES	100		600
3	1111.850200	APPLES	800		1111.8502	APPLES	200		800
4					1111.8502	APPLES	300
5					1111.8502	ORANGES	400
6					1111.8502	BANANAS	500
7					1111.850200	APPLES	800
8
4f

Cell Formulas
Range	Formula
C2:C3	C2	=SUMPRODUCT(--($E$2:$E$20=A2),--($F$2:$F$20=B2),($G$2:$G$20))
I2:I3	I2	=SUM(($E$2:$E$20=A2)*($F$2:$F$20=B2)*($G$2:$G$20))

 

[mj_1986]

Hi Dante, thanks for this! How have you formatted the data because I am still getting the same result as before where as you as have the correct result.

Or is this something to do with 'limiting the range'? How do I do this?

 

[mj_1986]

Please edit your profile to show the version of Excel that you use.
I included a formula that requires a fairly recent version of Excel.
I assumed columns A and E are text

Sumifs.xlsm
	A	B	C	D	E	F	G	H	I
1	Text				Text
2	1111.8502	APPLES	600		1111.8502	APPLES	100		600
3	1111.850200	APPLES	800		1111.8502	APPLES	200		800
4					1111.8502	APPLES	300
5					1111.8502	ORANGES	400
6					1111.8502	BANANAS	500
7					1111.850200	APPLES	800
8
4f

Cell Formulas
Range	Formula
C2:C3	C2	=SUMPRODUCT(--($E$2:$E$20=A2),--($F$2:$F$20=B2),($G$2:$G$20))
I2:I3	I2	=SUM(($E$2:$E$20=A2)*($F$2:$F$20=B2)*($G$2:$G$20))

Is there a way to Further distinguish between APPLES and Apples? Ie. tacking into account whether its upper case or lower case?

 

[Dave Patton]

What version of Excel are you using?
" Is there a way to Further distinguish between APPLES and Apples? Ie. tacking into account whether its upper case or lower case?
The formula may not be very efficient.


Revise to your requirements

Sumifs.xlsm
	A	B	C	D	E	F	G	H	I
1	Text				Text
2	1111.8502	APPLES	600		1111.8502	APPLES	100		100
3	1111.850200	APPLES	800		1111.8502	APPLES	200		200
4					1111.8502	apples	300		0
5					1111.8502	ORANGES	400		0
6					1111.8502	BANANAS	500		0
7					1111.850200	APPLES	800		0
8
4f

Cell Formulas
Range	Formula
C2:C3	C2	=SUMPRODUCT(--($E$2:$E$20=A2),--($F$2:$F$20=B2),($G$2:$G$20))
I2:I7	I2	=AND(EXACT(F2,B$2),E2=A$2)*G2