SUMIF multiple columns

  • Thread starter Thread starter Jayme
  • Start date Start date
J

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
 
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
 
Back
Top