Summing with Multiple Criteria --> Sum Changes with Filter

[excelNewbie007]

I'm trying to find the visible sum of the QUANTITY of GOOD units that aren't DISPOSED yet. If I decide to filter any of the other columns, I want the sum to change accordingly since I'm looking for the visible sum.

Range I want to sum: QUANTITY
A2:A1000

Criteria: CONDITION & DISPOSAL DATE
B2:B1000 = "Good"
C2:C1000 = " "

Notes:
Only sum visible cells, depending on if I apply the filter.

The only functions I know that only measure visible cells are SUBTOTAL and SUMPRODUCT. I've been playing around with these functions, but I can't seem to get it. Like I tried using subtotal(9,____). In the ____, I tried to write a function that followed the criteria AND returned a range, but I was unable to find that. I'm not familiar with pivot tables or name ranges, so if it's possible, stray away from those.

[TABLE="width: 696"]
<TBODY>[TR]
[TD]Quantity</SPAN>[/TD]
[TD](A)[/TD]
[TD]Condition (B)</SPAN>[/TD]
[TD][/TD]
[TD]Disposal Date (C)</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD][/TD]
[TD]Good</SPAN>[/TD]
[TD]</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD][/TD]
[TD]Good</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD][/TD]
[TD]Good</SPAN>[/TD]
[TD][/TD]
[TD]7/19/2012</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD][/TD]
[TD]Bad</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD][/TD]
[TD]Good</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD][/TD]
[TD]Good</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD][/TD]
[TD]Good</SPAN>[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[T. Valko]

I'm trying to find the visible sum of the QUANTITY of GOOD units that aren't DISPOSED yet. If I decide to filter any of the other columns, I want the sum to change accordingly since I'm looking for the visible sum.

Range I want to sum: QUANTITY
A2:A1000

Criteria: CONDITION & DISPOSAL DATE
B2:B1000 = "Good"
C2:C1000 = " "

Notes:
Only sum visible cells, depending on if I apply the filter.

The only functions I know that only measure visible cells are SUBTOTAL and SUMPRODUCT. I've been playing around with these functions, but I can't seem to get it. Like I tried using subtotal(9,____). In the ____, I tried to write a function that followed the criteria AND returned a range, but I was unable to find that. I'm not familiar with pivot tables or name ranges, so if it's possible, stray away from those.

[TABLE="width: 696"]
<TBODY>[TR]
[TD]Quantity</SPAN>
[/TD]
[TD](A)
[/TD]
[TD]Condition (B)</SPAN>
[/TD]
[TD][/TD]
[TD]Disposal Date (C)</SPAN>
[/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD][/TD]
[TD]Good</SPAN>
[/TD]
[TD]</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3</SPAN>
[/TD]
[TD][/TD]
[TD]Good</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD][/TD]
[TD]Good</SPAN>
[/TD]
[TD][/TD]
[TD]7/19/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD][/TD]
[TD]Bad</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2</SPAN>
[/TD]
[TD][/TD]
[TD]Good</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5</SPAN>
[/TD]
[TD][/TD]
[TD]Good</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1</SPAN>
[/TD]
[TD][/TD]
[TD]Good</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Try this...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A2,ROW(A2:A1000)-ROW(A2),0)),--(B2:B1000="Good"),--(C2:C1000=" "))

 

[Oeldere]

See the Excel-example in the free (dutch) link.

http://www.mijnbestand.nl/share.php?key=PTMAFQNN3WE3
I used:

1) subototal in combination with filters
2) pivottable

If you have (other) questions, just ask.<o></o>

 

[T. Valko]

Try this...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A2,ROW(A2:A1000)-ROW(A2),0)),--(B2:B1000="Good"),--(C2:C1000=" "))

If you have formulas in column C that return " " (space) you should change it so that the formula returns "" (a blank) instead.

 

[Mindpsyche]

If you have formulas in column C that return " " (space) you should change it so that the formula returns "" (a blank) instead.

Hey don't mean to hijack the thread but could you explain how the ROW(A2-A1000)-Row(A2) works?

 

[T. Valko]

Hey don't mean to hijack the thread but could you explain how the ROW(A2-A1000)-Row(A2) works?

We have to step through the data one row at a time to get a subtotal for each visible row.

So, we offset the starting cell in the range by n rows. Where n=

ROW(A2)-Row(A2) = 0
ROW(A3)-Row(A2) = 1
ROW(A4)-Row(A2) = 2
ROW(A5)-Row(A2) = 3
ROW(A6)-Row(A2) = 5
ROW(A7)-Row(A2) = 6
etc
etc
etc

 

[Mindpsyche]

Nice, thanks

 

[T. Valko]

We have to step through the data one row at a time to get a subtotal for each visible row.

So, we offset the starting cell in the range by n rows. Where n=

ROW(A2)-Row(A2) = 0
ROW(A3)-Row(A2) = 1
ROW(A4)-Row(A2) = 2
ROW(A5)-Row(A2) = 3
ROW(A6)-Row(A2) = 5
ROW(A7)-Row(A2) = 6
etc
etc
etc

Looks like the sequence is out of sorts!

The correct sequence should be:

ROW(A2)-Row(A2) = 0
ROW(A3)-Row(A2) = 1
ROW(A4)-Row(A2) = 2
ROW(A5)-Row(A2) = 3
ROW(A6)-Row(A2) = 4
ROW(A7)-Row(A2) = 5
etc
etc

 

[T. Valko]

Nice, thanks

You're welcome!

 

[excelNewbie007]

try this...

=sumproduct(subtotal(9,offset(a2,row(a2:a1000)-row(a2),0)),--(b2:b1000="good"),--(c2:c1000=" "))

it worked! Thank you!