SUMIF multiple columns

[Jayme]

Here's what I want to do:

A B C D
1 Shop# 2004 2003 % Change

2 101 50 40 25%

3 102 40 0 N/A

4 103 0 20 N/A

5 104 20 45 -56%
_______________________________

6 ???? xxxx

???? (B6) = I only want to add the numbers in column B if the shop has
a number greater than 0 for both 2004 (B2) & 2003 (C2).

So the amount that ???? (B6) equals would be 70.

xxxx (C6) = I only want to add the numbers in column C if the shop has
a number greater than 0 for both 2004 (B2) & 2003 (C2).

So the amount that xxxx (C6) equals would be 85.

I think you need to use a SUMIF command, but can't quite get it.
Please let me know how to do this.

Thanks for all your help!!!

JR

 

[Andy Brown]

So the amount that ???? (B6) equals would be 70.

So the amount that xxxx (C6) equals would be 85.

You can do this with an array formula, as cribbed from
http://j-walk.com/ss/excel/tips/tip74.htm. In B6,

=SUM((B2:B5>0)*(C2:C5>0)*B2:B5)

In C6,

=SUM((B2:B5>0)*(C2:C5>0)*C2:C5)

To array-enter, use CTRL+Shift+Enter, rather than plain Enter.

Rgds,
Andy

 

[Guest]

Jayme
Another way without using an array formula

=SUMPRODUCT((B2:B5>0)*(C2:C5>0)*(B2:B5)
an
=SUMPRODUCT((B2:B5>0)*(C2:C5>0)*(C2:C5)

Good Luck
Mark Graesse
(e-mail address removed)

----- Jayme wrote: ----

Here's what I want to do

A B C
1 Shop# 2004 2003 % Chang

2 101 50 40 25

3 102 40 0 N/

4 103 0 20 N/

5 104 20 45 -56
______________________________

6 ???? xxx

???? (B6) = I only want to add the numbers in column B if the shop ha
a number greater than 0 for both 2004 (B2) & 2003 (C2)

So the amount that ???? (B6) equals would be 70.

xxxx (C6) = I only want to add the numbers in column C if the shop ha
a number greater than 0 for both 2004 (B2) & 2003 (C2)

So the amount that xxxx (C6) equals would be 85

I think you need to use a SUMIF command, but can't quite get it.
Please let me know how to do this

Thanks for all your help!!

J