Sumifs formula to return blank not 0

[Mallesh23]

Hi Team,

Need excel formula help in sumifs or any other alternative I am fine.
if Data is proper as shown in Situation A, Getting Correct Output shown in Range(L1:N7)

Situation-2
I have some blank cell value as criteria ......I should get result shown in L15:N15
If Criteria are complete blank ......formula should give blank output not zero.

I have very long list Needs a correct formula to achive the task


Below sample Data with expected output

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Situation1		Data Salary					Criteria				Output IND Currency
2	Country	Player	Q1	Q2	Q3	Currency		Country	Player	Currency		Q1	Q2	Q3
3	India	Sachin	5000	10	100	IND		India	Sachin	IND		5000	10	100
4	Australia	Ponting	6000	20	200	IND		Australia	Ponting	AUD		9000	40	400
5	India	Dhoni	8000	30	300	IND		India	Sachin	IND		5000	10	100
6	Australia	Ponting	9000	40	400	AUD		Australia	Ponting	AUD		9000	40	400
7	India	Sehwag	10000	50	500	IND		India	Sehwag	IND		10000	50	500
8
9
10
11
12	Situation2		Data Salary					Criteria				Output IND Currency
13	Country	Player	Q1	Q2	Q3	Currency		Country	Player	Currency		Q1	Q2	Q3
14	India	Sachin	5000	10	100	IND		India	Sachin	IND		5000	10	100
15	Australia	Ponting	6000	20	200			Australia	Ponting			6000	20	200	Expected Output
16	India	Dhoni	8000	30	300	IND		India	Sachin	IND		5000	10	100
17	Australia	Ponting	9000	40	400	AUD		Australia	Ponting	AUD		9000	40	400
18	India	Sehwag	10000	50	500	IND						0	0	0	Expected Blank here
Salary

Cell Formulas
Range	Formula
L16:L18,L14,L3:L7	L3	=SUMIFS($C$3:$C$7,$A$3:$A$7,$H3,$B$3:$B$7,$I3,$F$3:$F$7,$J3)
M16:M18,M14,M3:M7	M3	=SUMIFS($D$3:$D$7,$A$3:$A$7,$H3,$B$3:$B$7,$I3,$F$3:$F$7,$J3)
N16:N18,N14,N3:N7	N3	=SUMIFS($E$3:$E$7,$A$3:$A$7,$H3,$B$3:$B$7,$I3,$F$3:$F$7,$J3)

Thanks
mg

 

[KRice]

Here is one approach. There are several ways to display blanks rather than 0's. I used a custom number format that involves selecting the entire answer range, right click Format Cells > Number > Custom, then paste 0;–0;;@ into the "Type" box and confirm with OK.

MrExcel20210428.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
12	Situation2		Data Salary					Criteria				Output IND Currency
13	Country	Player	Q1	Q2	Q3	Currency		Country	Player	Currency		Q1	Q2	Q3
14	India	Sachin	5000	10	100	IND		India	Sachin	IND		5000	10	100
15	Australia	Ponting	6000	20	200			Australia	Ponting			6000	20	200
16	India	Dhoni	8000	30	300	IND		India	Sachin	IND		5000	10	100
17	Australia	Ponting	9000	40	400	AUD		Australia	Ponting	AUD		9000	40	400
18	India	Sehwag	10000	50	500	IND
Sheet6

Cell Formulas
Range	Formula
L14:N18	L14	=SUMPRODUCT(INDEX($C$14:$E$18,,MATCH(L$13,$C$13:$E$13,0)),($A$14:$A$18=$H14)*($B$14:$B$18=$I14)*($F$14:$F$18=$J14))

 

[Zot]

TextBox Line.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Situation1		Data Salary					Criteria				Output IND Currency
2	Country	Player	Q1	Q2	Q3	Currency		Country	Player	Currency		Q1	Q2	Q3
3	India	Sachin	5000	10	100	IND		India	Sachin	IND		5000	10	100
4	Australia	Ponting	6000	20	200	IND		Australia	Ponting	AUD		9000	40	400
5	India	Dhoni	8000	30	300	IND		India	Sachin	IND		5000	10	100
6	Australia	Ponting	9000	40	400	AUD		Australia	Ponting	AUD		9000	40	400
7	India	Sehwag	10000	50	500	IND		India	Sehwag	IND		10000	50	500
Sheet1

Cell Formulas
Range	Formula
L3:N3	L3	=IF(AND($H3="",$I3="",$J3=""),"",SUMPRODUCT(($A3:$A7=$H3)*($B3:$B7=$I3)*($F3:$F7=$J3)*1,C3:C7))
L4:N4	L4	=IF(AND($H4="",$I4="",$J4=""),"",SUMPRODUCT(($A3:$A7=$H4)*($B3:$B7=$I4)*($F3:$F7=$J4)*1,C3:C7))
L5:N5	L5	=IF(AND($H5="",$I5="",$J5=""),"",SUMPRODUCT(($A3:$A7=$H5)*($B3:$B7=$I5)*($F3:$F7=$J5)*1,C3:C7))
L6:N6	L6	=IF(AND($H6="",$I6="",$J6=""),"",SUMPRODUCT(($A3:$A7=$H6)*($B3:$B7=$I6)*($F3:$F7=$J6)*1,C3:C7))
L7:N7	L7	=IF(AND($H7="",$I7="",$J7=""),"",SUMPRODUCT(($A3:$A7=$H7)*($B3:$B7=$I7)*($F3:$F7=$J7)*1,C3:C7))

 

[Mallesh23]

Hi zote and Krice,

Thanks for your help, I tried sumproduct its working, but when I try formula for multiple cells it produce single value, on actual project.

I am Not good in Sumproduct can you suggest sumifs formula.





Thanks
mg

 

[Zot]

Hi zote and Krice,

Thanks for your help, I tried sumproduct its working, but when I try formula for multiple cells it produce single value, on actual project.

I am Not good in Sumproduct can you suggest sumifs formula.





Thanks
mg

Not sure what you meant by multiple cell single value.

Actually that SUMPRODUCT is very straightforward
SUMPRODUCT(($A3:$A7=$H3)*($B3:$B7=$I3)*($F3:$F7=$J3)*1,C3:C7).
If
($A3:$A7=$H3) = TRUE
($B3:$B7=$I3) = TRUE
($F3:$F7=$J3) = TRUE
then
TRUE*TRUE*TRUE=1

Maybe I should have removed *1 in
SUMPRODUCT(($A3:$A7=$H3)*($B3:$B7=$I3)*($F3:$F7=$J3)*1,C3:C7)

so it is just
SUMPRODUCT(1,C3:C7) or SUM (1*C3:C7)

I will try SUMIF later

 

[Zot]

Using SUMIFS
Opps copy wrong one

 

[Zot]

SUMPRODUCT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Situation1		Data Salary					Criteria				Output IND Currency
2	Country	Player	Q1	Q2	Q3	Currency		Country	Player	Currency		Q1	Q2	Q3
3	India	Sachin	5000	10	100	IND		India	Sachin	IND		5000	10	100
4	Australia	Ponting	6000	20	200	IND		Australia	Ponting	AUD		9000	40	400
5	India	Dhoni	8000	30	300	IND		India	Sachin	IND		5000	10	100
6	Australia	Ponting	9000	40	400	AUD		Australia	Ponting	AUD		9000	40	400
7	India	Sehwag	10000	50	500	IND		India	Sehwag	IND		10000	50	500
Sheet1

Cell Formulas
Range	Formula
L3:N3	L3	=IF(AND($H3="",$I3="",$J3=""),"",SUMIFS(C3:C7,$A$3:$A$7,$H3,$B$3:$B$7,$I3,$F$3:$F$7,IF(ISBLANK($J3),"",$J3)))
L4:N4	L4	=IF(AND($H4="",$I4="",$J4=""),"",SUMIFS(C3:C7,$A$3:$A$7,$H4,$B$3:$B$7,$I4,$F$3:$F$7,IF(ISBLANK($J4),"",$J4)))
L5:N5	L5	=IF(AND($H5="",$I5="",$J5=""),"",SUMIFS(C3:C7,$A$3:$A$7,$H5,$B$3:$B$7,$I5,$F$3:$F$7,IF(ISBLANK($J5),"",$J5)))
L6:N6	L6	=IF(AND($H6="",$I6="",$J6=""),"",SUMIFS(C3:C7,$A$3:$A$7,$H6,$B$3:$B$7,$I6,$F$3:$F$7,IF(ISBLANK($J6),"",$J6)))
L7:N7	L7	=IF(AND($H7="",$I7="",$J7=""),"",SUMIFS(C3:C7,$A$3:$A$7,$H7,$B$3:$B$7,$I7,$F$3:$F$7,IF(ISBLANK($J7),"",$J7)))

 

[KRice]

@Mallesh23, I suspect you are getting a single value because the cell references need to be adjusted for your actual project. It sounds as if you may have all of the cell references locked (with the $ symbol). I noticed in your original post (that used SUMIFS) that the "Situation 2" block still referenced source information in the Situation 1 block, rather than source data found in columns A, B, C, and F in the Situation 2 table, as shown by the highlighted cells in this image. Here I selected cell L14 (an output in the Situation 2 section) and clicked on the formula in the formula bar to highlight the cells used. Have you confirmed that the formulas are referring to the correct ranges?

 

[Zot]

@Mallesh23, I suspect you are getting a single value because the cell references need to be adjusted for your actual project. It sounds as if you may have all of the cell references locked (with the $ symbol). I noticed in your original post (that used SUMIFS) that the "Situation 2" block still referenced source information in the Situation 1 block, rather than source data found in columns A, B, C, and F in the Situation 2 table, as shown by the highlighted cells in this image. Here I selected cell L14 (an output in the Situation 2 section) and clicked on the formula in the formula bar to highlight the cells used. Have you confirmed that the formulas are referring to the correct ranges?
View attachment 37758

Yes, I saw that but I thought he was aware of it