Sumif blank row and nearest value

[samitnair]

Hello Everyone,

I am using sumif function to calculate an amount based on criteria (A2:D2) but whenever there is a blank cell in the Region column the result turns to be "Zero". I am thinking if we can skip the blank cell to get the result.

I tried the "*" and "<>" but ends up with too many arguments or #Value .

	A	B	C	D	E	F
1	Name	Type	Resource	Region	Quantity	Amount
2	AAA		123	US	100	=SUMIFS('EA Report'!$L:$L,'EA Report'!$C:$C,Play!$A2,'EA Report'!$D:$D,Play!$C2,'EA Report'!$J:$J,Play!D2)
3	BBB		456	UK	50
4	GGG		456		125	Blank or Zero

<tbody> </tbody>

 

[samitnair]

Can anyone give a try on correcting my formula

 

[Aladin Akyurek]

Not very clear. Do you mean this?

=SUMIFS('EA Report'!$L:$L,'EA Report'!$C:$C,$A2,'EA Report'!$D:$D,$C2,'EA Report'!$J:$J,IF($D2="","?*",$D2))

The formula is assumed to be located in the Play sheet.
​

 

[samitnair]

Hello Aladin,



EA Report

	C	D	J	L
1	Name	Resource	Region	Amount
2	AAA	123	US	100
3	BBB	456	UK	50
4	GGG <tbody> </tbody>	456	Japan	200

<tbody>
</tbody>

Play

	A	B	C	D	E	F
1	Name	Type	Resource	Region	Quantity	Amount
2	AAA		123	US	100	=SUMIFS('EA Report'!$L:$L,'EA Report'!$C:$C,Play!$A2,'EA Report'!$D:$D,Play!$C2,'EA Report'!$J:$J,Play!D2)
3	BBB		456	UK	50
4	GGG		456		125	Blank or Zero

<tbody>
</tbody>

So if I consider Row 3, Sumifs will search in EA report and match A3,C3,D3 and sum the value. In sheet "EA report" all the cells have value but in sheet play the region can be empty/blank. I want sumif to skip the blank (if any) and give me the nearest value which matches the other criteria. I have given the below example in sheet play (Japan is missing but it still gave 200). Any such suggestions

	A	B	C	D	E	F
1	Name	Type	Resource	Region	Quantity	Amount
4	GGG		456		125	200

<tbody>
</tbody>

I am open to change the format or formula and more than glad to learn excel. I appreciate your time.

 

[Aladin Akyurek]

What do you want there for GGG - 200 or 0?

 

[samitnair]

I want there 200.

 

[Aladin Akyurek]

I want there 200.

That is what the formula already propsed does...

EA Report

Name	Resource						Region		Amount
AAA	123						US		100
BBB	456						UK		50
GGG	456						Japan		200

<tbody>
</tbody>

Play

Name	Type	Resource	Region	Quantity	Amount
AAA		123	US	100	100
BBB		456	UK	50	50
GGG		456		125	200

<tbody>
</tbody>

In F2 enter and copy down:

=SUMIFS('EA Report'!$L:$L,'EA Report'!$C:$C,$A2,'EA Report'!$D:$D,$C2,'EA Report'!$J:$J,IF($D2="","?*",$D2))