Sumifs & isblank

[khersh]

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.

 

[Aladin Akyurek]

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.

Your specs suggest a set up like:

(Date)	(estimated)	(actual)	(quantity)	total
9/1/2013	9/1/2013	9/2/2013	100	0
9/2/2013	9/6/2013	9/6/2013	50	100
9/3/2013	9/12/2013		100	0
9/4/2013	9/10/2013		65	100
9/5/2013	9/15/2013	9/15/2013	7	0
9/6/2013	9/4/2013		100	50

<tbody>
</tbody>

E2, control+shift+enter, not just enter, and copy down:

=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.

 

[khersh]

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:

Location Code	Estimated Arrival Date	Placed Date	Shipment Quantity
A	9/21/2013		10
A	9/21/2013	9/22/2013	10
B	9/30/2013		10
C	9/25/2013		10
D	9/25/2013		10
E	9/25/2013		10
F	9/25/2013		10
A	9/27/2013	9/25/2013	10
A	9/26/2013	9/25/2013	10
A	9/25/2013		10
A	9/27/2013		10

<tbody>
</tbody>

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:

Current Date	Shipment Quantity
9/21/2013	10
9/22/2013	10
9/23/2013	0
9/24/2013	0
9/25/2013	30
9/26/2013	0
9/27/2013	10
9/28/2013	0
9/29/2013	0
9/30/2013	0

<colgroup><col><col></colgroup><tbody>
</tbody>

THANKS FOR YOUR HELP!!!

 

[markmzz]

Try this (a small modification in Aladin's formula):

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

 

[Aladin Akyurek]

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:

Location Code	Estimated Arrival Date	Placed Date	Shipment Quantity
A	9/21/2013		10
A	9/21/2013	9/22/2013	10
B	9/30/2013		10
C	9/25/2013		10
D	9/25/2013		10
E	9/25/2013		10
F	9/25/2013		10
A	9/27/2013	9/25/2013	10
A	9/26/2013	9/25/2013	10
A	9/25/2013		10
A	9/27/2013		10

<TBODY>
</TBODY>

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:

Current Date	Shipment Quantity
9/21/2013	10
9/22/2013	10
9/23/2013
9/24/2013
9/25/2013	30
9/26/2013
9/27/2013	10
9/28/2013
9/29/2013
9/30/2013

<TBODY>
</TBODY>

THANKS FOR YOUR HELP!!!

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...

Current Date	A
9/21/2013	10
9/22/2013	10
9/23/2013	0
9/24/2013	0
9/25/2013	30
9/26/2013	0
9/27/2013	10
9/28/2013	0
9/29/2013	0
9/30/2013	0

<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>
</TBODY>

B2, control+shift+enter, not just enter, and copy down:

=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.