Suming with multiple conditions

[Jared]

I am trying to sum up a list of numbers where there are
two limiting factors. I have a column of number of jobs
produced by 3 different ways: new, retained, or
expansion. These are coded in a column by the first
letter of each word (N, R, E). I also want to limit the
summing of jobs by wether these are businesses or
industries, which are also coded in a separate column by
the first letter of each word (B or I). I need to create
an equation that will limit the number of jobs by both of
these factors and i don't care which order they are
limited in. I hope someone can help because I am
completely stuck.

 

[Andy B]

Hi

Try using SUMPRODUCT:
=SUMPRODUCT((A2:A1000="N")*(B2:B1000="B")*(C2:C1000))
You cannot use full column references with this function, and the ranges
must the same size.

 

[Frank Kabel]

Hi
try something like
=SUMPRODUCT(--(A1:A100="N"),--(B1:B100="B"),C1:C100)

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(A2:A100="N"),--(B2:B100="B"),C2:C100)

adapt to fit your real ranges, column C obviously holds the numbers in this
example
also to make it more dynamic replace "N" and "B" with cell references where
you would
type the criteria, that way you don't have to edit the formula each time you
change criteria
e.g.

=SUMPRODUCT(--(A2:A100=D2),--(B2:B100=E2),C2:C100)

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom

 

[Guest]

Thank you for the help but when i type this in with the
ranges changed for my needs i get a #VALUE! error? Do you
know why?

 

[Frank Kabel]

Hi
could you post your exavct formula. Also do you have error values
within these ranges?

 

[Jared]

"Type of "Type of Job
Building a" Growth b" Jobs c
B E 10
B N 20
Subtotal 30
B N 60
Subtotal 60
B N 676 f
B N 120 g
B N 110 h
Subtotal 906
B R 17
B N 200 i
B R 6
I E 17
Subtotal 240
B N 100
B N 15
B E --
Subtotal 115
B N 500
Subtotal 500
Total 1,851
The letters next to the numbers represent footnotes. I am
using excel 97 and could not figure out how to create
footnotes so I formatted the cells and had them display
the letter no matter what value was entered. The value
entered is only the numbers so that shouldn't mess up the
calculation. The equation i used is below:
=SUMPRODUCT(--(F3:F21="I"),--(G3:G21="E"),--H3:H21)
I also used other equations to count the number of
businesses and industries but i doubt they will help or
pretain at all. They are below:
=COUNTIF(F3:F21, "B")
=COUNTIF(F3:F21, "I")
If you need anything else just ask. Thanks so much for
the help.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(F3:F21="I"),--(G3:G21="E"),H3:H21)

Instead of using this kind of footnotes I would use 'Insert - Comment'

 

[jared]

thank you for all the help it worked out fine.