Sumifs & isblank

K

khersh

New Member
Joined
Sep 16, 2013
Messages
4
Hi -

I am trying to create a condition in a SUMIFS function that looks at works as follows:

A B C D
(Date) (estimated) (actual) (quantity)
1 9/1/13 9/1/13 9/02/13 100
2 9/2/13 9/6/13 9/06/13 50
3 9/3/13 9/12/13 100
4 9/4/13 9/10/13 65
5 9/5/13 9/15/13 9/15/13 7
6 9/6/13 9/4/13 100


SUM Range: Column D (Quantity)
Criteria Range: Column A (Date)

***This is where i need help**

Criteria: If column C is not blank and equals A1, then sum values in Column D ... but if Column C is blank then (and only then) look at Column B and if it equals A1 then sum values in Column D.

Thanks for any help.
 
Sort by date Sort by votes
khersh said:
Hi -

I am trying to create a condition in a SUMIFS function that looks at works as follows:

A B C D
(Date) (estimated) (actual) (quantity)
1 9/1/13 9/1/13 9/02/13 100
2 9/2/13 9/6/13 9/06/13 50
3 9/3/13 9/12/13 100
4 9/4/13 9/10/13 65
5 9/5/13 9/15/13 9/15/13 7
6 9/6/13 9/4/13 100


SUM Range: Column D (Quantity)
Criteria Range: Column A (Date)

***This is where i need help**

Criteria: If column C is not blank and equals A1, then sum values in Column D ... but if Column C is blank then (and only then) look at Column B and if it equals A1 then sum values in Column D.

Thanks for any help.
Click to expand...

Your specs suggest a set up like:


[TABLE="width: 250"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"](Date)[/TD]
[TD="class: xl66, width: 74, bgcolor: transparent"](estimated)[/TD]
[TD="class: xl66, width: 69, bgcolor: transparent"](actual)[/TD]
[TD="class: xl66, width: 63, bgcolor: transparent"](quantity)[/TD]
[TD="class: xl68, width: 64, bgcolor: transparent"]total[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]9/1/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9/1/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9/2/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]9/2/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9/6/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9/6/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]9/3/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9/12/2013[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]9/4/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9/10/2013[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]65[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]100[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]9/5/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9/15/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9/15/2013[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]9/6/2013[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9/4/2013[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]


E2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=SUM(IF(IF($C$2:$C$7="",$B$2:$B$7,$C$2:$C$7)=$A2,$D$2:$D$7))

If this is not what you have in mind, please try to post the results you expect.
 
Upvote 0
Thanks Aladin ---

This works but I am running into an issue because i need to add additional conditions. Basically I have a sheet where I want to sum incoming shipments on a given date, I sum the shipments based on two criteria the date the shipment arrives and the point of arrival (I will run the same formula on different sheets representing different points of arrival). I am fine with the condition for arrival point as it is very simple. The difficulty I am having is telling excel to sum entries with a given "placed date", but if placed date is blank then sum entries with a given "estimated arrival date". A representation would be as follows:

Data:

[TABLE="class: grid, width: 624"]
<tbody>[TR]
[TD]Location Code[/TD]
[TD]Estimated Arrival Date[/TD]
[TD]Placed Date[/TD]
[TD]Shipment Quantity[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]9/21/2013[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]9/21/2013[/TD]
[TD="align: right"]9/22/2013[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]9/30/2013[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]9/25/2013[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]9/25/2013[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]9/25/2013[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]9/25/2013[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]9/27/2013[/TD]
[TD="align: right"]9/25/2013[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]9/26/2013[/TD]
[TD="align: right"]9/25/2013[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]9/25/2013[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]9/27/2013[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]


From this data I would want to run a formula to show summed shipment quantities for entries with a "placed date" matching Column A "current date" (below) and a "location code" of A. If "placed date" is blank I would want the formula to look at "estimated arrival date" to find matches with "current date". The output I would expect with the formula being input in column b "Shipment Quantity" (below) would be as follows:

Output of Table:

[TABLE="class: grid, width: 271"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Current Date[/TD]
[TD]Shipment Quantity[/TD]
[/TR]
[TR]
[TD="align: right"]9/21/2013[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]9/22/2013[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]9/23/2013[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/24/2013[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/25/2013[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]9/26/2013[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/27/2013[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]9/28/2013[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/29/2013[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2013[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]



THANKS FOR YOUR HELP!!!
 
Upvote 0
Try this (a small modification in Aladin's formula):

Code: 
Use Ctrl+Shift+Enter and not only Enter to enter the fomula

B16-> =SUM(IF(IF($A$2:$A$12="A",IF($C$2:$C$12="",$B$2:$B$12,$C$2:$C$12))=$A16,$D$2:$D$12))

With the Output of Table in the range A15:B25

Markmzz
 
Upvote 0
khersh said:
Thanks Aladin ---

This works but I am running into an issue because i need to add additional conditions. Basically I have a sheet where I want to sum incoming shipments on a given date, I sum the shipments based on two criteria the date the shipment arrives and the point of arrival (I will run the same formula on different sheets representing different points of arrival). I am fine with the condition for arrival point as it is very simple. The difficulty I am having is telling excel to sum entries with a given "placed date", but if placed date is blank then sum entries with a given "estimated arrival date". A representation would be as follows:

Data:

[TABLE="class: grid, width: 624"]
<TBODY>[TR]
[TD]Location Code
[/TD]
[TD]Estimated Arrival Date
[/TD]
[TD]Placed Date
[/TD]
[TD]Shipment Quantity
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]9/21/2013
[/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]9/21/2013
[/TD]
[TD="align: right"]9/22/2013
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]9/30/2013
[/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD="align: right"]9/25/2013
[/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD="align: right"]9/25/2013
[/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD="align: right"]9/25/2013
[/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD="align: right"]9/25/2013
[/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]9/27/2013
[/TD]
[TD="align: right"]9/25/2013
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]9/26/2013
[/TD]
[TD="align: right"]9/25/2013
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]9/25/2013
[/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]9/27/2013
[/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[/TR]
</TBODY>[/TABLE]


From this data I would want to run a formula to show summed shipment quantities for entries with a "placed date" matching Column A "current date" (below) and a "location code" of A. If "placed date" is blank I would want the formula to look at "estimated arrival date" to find matches with "current date". The output I would expect with the formula being input in column b "Shipment Quantity" (below) would be as follows:

Output of Table:

[TABLE="class: grid, width: 271"]
<TBODY>[TR]
[TD]Current Date
[/TD]
[TD]Shipment Quantity
[/TD]
[/TR]
[TR]
[TD="align: right"]9/21/2013
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD="align: right"]9/22/2013
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD="align: right"]9/23/2013
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9/24/2013
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9/25/2013
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD="align: right"]9/26/2013
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9/27/2013
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD="align: right"]9/28/2013
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9/29/2013
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2013
[/TD]
[TD="align: right"][/TD]
[/TR]
</TBODY>[/TABLE]



THANKS FOR YOUR HELP!!!
Click to expand...

You'll need to create either an additional column for the location code in your output table (a reasonable option) or a separate output table...

Let Data, A:D, house the source data

Let a different sheet in A:C house the output table for processing...

[TABLE="width: 207"]
<COLGROUP><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5063" width=142><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4721" width=133><TBODY>[TR]
[TD="class: xl66, width: 142, bgcolor: white"]Current Date[/TD]
[TD="class: xl67, width: 133, bgcolor: white"]A[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/21/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/22/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/23/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/24/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/25/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/26/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/27/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/28/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/29/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 142, bgcolor: white"]9/30/2013[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
</TBODY>[/TABLE]

B2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=SUM(IF(Data!$A$2:$A$12=B$1,
  IF(IF(Data!$C$2:$C$12="",Data!$B$2:$B$12,Data!$C$2:$C$12)=$A2,Data!$D$2:$D$12)))

Note that, if you add B next to A, the formula can also be copied across.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Required Transposed Values instead of Row Items of Duplicate Invoice no with Each GSTIN which is displaying the SUM of 12+18+28
Replies
1
Views
115
rk_rk
R
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
214
Peter_SSs
Peter_SSs
O
  • Solved
Conditional Format Compare Ranges
Replies
3
Views
122
Fluff
Fluff
T
  • Question Question
COUNTIFS with on Dates
Replies
6
Views
223
Alex Blakenburg
A
H
  • Solved
Copy or Transpose multiple columns into a single column.
Replies
2
Views
127
harzer
H

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