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