Repost of SUMIF Question for Frank

[Ric]

Hi Ric
I'm a little bit confused
could you post some example rows of your data (plain text please - no
attchment)

Posting from Home now. Thanks for your time Frank.

example:

A ... E F G
H
Jan 04 Feb 04 Mar 04
Apr 04
1 39H150 322 350 340 320
2 49H223 120 100 140 40
3 39H157 800 950 900 1000
4 39S100 45 90 130
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$200)

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

Regards

Ric

 

[Max]

hi, as Frank is probably recharging
his batteries right now <bg> ..

just wondering: why not just add together the SUMIFs
for 39H150 & 39H157 in the same cell?

Example:

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

 

[Frank Kabel]

Hi Max
thanks for stepping in. This was my solution in the
previous post. But if the OP wants only one formula he may
try
=SUMPRODUCT(--(cum_data!$C$1:$C$200=
{"39H150","39H157"}),cum_data!D$1$200)

 

[Ric]

Hi Max,

I tried this (Re: SUMIF for 2 or more variables 27/04/04 18:00 my time)
in said post. It didn't ome out with the correct values.

Ric

 

[Max]

Hi Max
thanks for stepping in. This was my solution in the
previous post...

Pleasure, Frank!
... sorry for the redundant suggestion though ..
(didn't know of your previous post to the OP)

 

[Frank Kabel]

Hi Max
really no problem with this double solution

 

[Max]

Tried both options and it seems to return correct results for me

Here's what I did ..

In a sheet named: cum_data
---------------------------------
The portion of your sample data below
was copy-pasted* into C1:E5
*and parsed using Data > Text to Columns

...............Jan-04..Feb-04
39H150.......322....350
49H223.......120....100
39H157.......800....950
39S100..........45......90

In another sheet, say Sheet2
-----------

a. Using 2 SUMIFs
------------------------

Placed in A2:

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

Copied A2 to B2

A2 returned 1122, B2 gave 1300 (correct results)


b. Using a slightly amended version of Frank's suggested formula
-----------------------------------------------------------------------

Placed in A3:

=SUMPRODUCT(--(cum_data!$C$1:$C$200={"39H150","39H157"})*cum_data!D$1$200)

A3 copied to B3

A3 returned 1122, B3 gave 1300 (identical correct results !)