Sumif blank row and nearest value

S

samitnair

Board Regular
Joined
Jul 5, 2010
Messages
155
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 .

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Resource[/TD]
[TD]Region[/TD]
[TD]Quantity[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AAA[/TD]
[TD][/TD]
[TD]123[/TD]
[TD]US[/TD]
[TD]100[/TD]
[TD]=SUMIFS('EA Report'!$L:$L,'EA Report'!$C:$C,Play!$A2,'EA Report'!$D:$D,Play!$C2,'EA Report'!$J:$J,Play!D2)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BBB[/TD]
[TD][/TD]
[TD]456[/TD]
[TD]UK[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GGG[/TD]
[TD][/TD]
[TD]456[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]Blank or Zero[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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.
​
 
Upvote 0
Hello Aladin,



EA Report
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]J[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Resource[/TD]
[TD]Region[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AAA[/TD]
[TD]123[/TD]
[TD]US[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BBB[/TD]
[TD]456[/TD]
[TD]UK[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GGG[TABLE="class: cms_table_grid, width: 500, align: center"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]456[/TD]
[TD]Japan[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]
Play

[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Resource[/TD]
[TD]Region[/TD]
[TD]Quantity[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AAA[/TD]
[TD][/TD]
[TD]123[/TD]
[TD]US[/TD]
[TD]100[/TD]
[TD]=SUMIFS('EA Report'!$L:$L,'EA Report'!$C:$C,Play!$A2,'EA Report'!$D:$D,Play!$C2,'EA Report'!$J:$J,Play!D2)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BBB[/TD]
[TD][/TD]
[TD]456[/TD]
[TD]UK[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GGG[/TD]
[TD][/TD]
[TD]456[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]Blank or Zero[/TD]
[/TR]
</tbody>[/TABLE]


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


[TABLE="class: cms_table_grid, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Resource[/TD]
[TD]Region[/TD]
[TD]Quantity[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]GGG[/TD]
[TD][/TD]
[TD]456[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]

I am open to change the format or formula and more than glad to learn excel. I appreciate your time.
 
Last edited:
Upvote 0
samitnair said:
I want there 200.
Click to expand...

That is what the formula already propsed does...

EA Report

[TABLE="class: grid, width: 640"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[TD="width: 64, bgcolor: transparent"]Resource[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Region[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Amount[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]AAA[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]123[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]US[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]BBB[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]456[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]UK[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]50[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]GGG[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]456[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Japan[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]

Play

[TABLE="class: grid, width: 426"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Resource[/TD]
[TD]Region[/TD]
[TD]Quantity[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD][/TD]
[TD="align: right"]123[/TD]
[TD]US[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD]UK[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]GGG[/TD]
[TD][/TD]
[TD="align: right"]456[/TD]
[TD][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]

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))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
SUMIFS Does Not Work With Blank Cells
Replies
6
Views
4K
masouder
M
V
  • Solved
Progressive average formula that counts also blank cells
2
Replies
17
Views
961
kvsrinivasamurthy
K
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
91
Fluff
Fluff
A
  • Solved
exclude zero, populate last amount for adjacent last enter cell for each range
Replies
6
Views
118
Alaa mg
A
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
364
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,970
Messages
6,175,713
Members
452,667
Latest member
vanessavalentino83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top