Sumif for 2 or more variables

  • Thread starter Thread starter kevin
  • Start date Start date
K

kevin

Hi is it possible to do a sumif for 2 or more variables
ie something like this

SUMIF(Data!A:A,OR("3199","3099","3399"),Data!AT:AT)

thanks in advance

Kevin
 
Hi Frank,

I am also trying to get a SUMIF to add 2 or more variables (strings) but
without any luck. I've tried the SUMPRODUCT you suggested but just get the
reply #Value in the cell where the calculation is taking place. Here is the
formula

=SUMPRODUCT(--('RPS monthly'!D2:D100={"39H130","39H157"}),'RPS
monthly'!AB2:AB100)

Did I do something stupidly wrong or will SUMPRODUCT only work on numbers
and not strings? Is there a formula to calculate the sum of 2 strings in a
column?

Regards

Ric
 
Hi
in your example column AB MUST contain only numbers.
How do you want to SUM strings?. e.g. what should be the
result of 'AB' + 'CD'?
 
Hi Frank,

Some contracts have more than one Contract Number. I have column "D"
that contains contract numbers. I have columns "M" > "EB" that contain
hourly forecasts for each contract number. The plan is to find certain
numbers (39H130 & 39H157) in column "D" and sum the hourly forecast that
coresponds with the contract number in columns "M" > "EB" on a seperate page
from the same line. This works with a single variable to find but i can't
get it to work when trying to find a second variable in the same formula.

I did try this;

=SUMIF(cum_data!$C$1:$C$200,"39H130",cum_data!D$1:D$200)+SUMIF(cum_data!$C$1
:$C$200,"39H157",cum_data!D$1:D$200)

It seems to accept this formula but doesn't give the correct result as it
does when chooseing autofilter/custom/equals 39H130 OR equals 39H157 and
having it subtotal at the bottom.

Regards

Ric
 
Hi Ric
I'm a little bit confused :-)
could you post some example rows of your data (plain text please - no
attchment)
 
Frank Kabel said:
Hi Ric
I'm a little bit confused :-)
could you post some example rows of your data (plain text please - no
attchment)
Click to expand...
Posting from Home now. Thanks for your time Frank.

example:

A ... E F G
H I
Jan 04 Feb 04 Mar 04
Apr 04 etc....
1 39H150 322 350 340 320
2 49H223 120 100 140 40
3 39H157 800 950 900 1000
4 39S100 45 90 130
150
5 etc...

A1 & A3 are different phases of the same job. The idea is to look down
column A to find all the 39H150's and 39H157's, look across the
corresponding rows and add the figures together. So in the example Jan 04
would equal 1,122 and Feb 04 would be 1,300, etc.

=SUMIF(cum_data!$C$1:$C$200,"39H130",cum_data!D$1:D$200)

That works for one contract number, what i'd like is it to find 2 contract
numbers and add them together.

Regards

Ric
 
Back
Top