FAO Mike

C

Caroline

Hi Mike

Sorry to bother you again. Can you see the problem with this formula? I
don't need to pick up a range of codes, I need a selection of different
codes, but from the same data as before. This is returning zero as the
answer. (Sorry it's long, but thought it was easier to send the whole thing!)

=+SUMPRODUCT((Actuals!A2:A630=5010111)*(Actuals!A2:A630=5010211)*(Actuals!A2:A630=5120111)*(Actuals!A2:A630=5200111)*(Actuals!A2:A630=5200211)*(Actuals!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actuals!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Actuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*(Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111)*(Actuals!A2:A630=5640111)*(Actuals!A2:A630=5640211)*(Actuals!C2:C630))

Thanks again
Caroline
 
B

Bob Phillips

You are doing an AND test instead of OR.

Try

=SUMPRODUCT((ACtuals!A2:A630={5010111,5010211,5120111,5200111,5200211,5260111,5260211,5400111,5400211,5400311,5401211,5440111,5480111,5640111,5640211})*(ACtuals!C2:C630))
 
G

Glenn

Caroline said:
Hi Mike

Sorry to bother you again. Can you see the problem with this formula? I
don't need to pick up a range of codes, I need a selection of different
codes, but from the same data as before. This is returning zero as the
answer. (Sorry it's long, but thought it was easier to send the whole thing!)

=+SUMPRODUCT((Actuals!A2:A630=5010111)*(Actuals!A2:A630=5010211)*(Actuals!A2:A630=5120111)*(Actuals!A2:A630=5200111)*(Actuals!A2:A630=5200211)*(Actuals!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actuals!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Actuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*(Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111)*(Actuals!A2:A630=5640111)*(Actuals!A2:A630=5640211)*(Actuals!C2:C630))

Thanks again
Caroline
Click to expand...


=SUMPRODUCT((
(Actuals!A2:A630=5010111)+(Actuals!A2:A630=5010211)+
(Actuals!A2:A630=5120111)+(Actuals!A2:A630=5200111)+
(Actuals!A2:A630=5200211)+(Actuals!A2:A630=5260111)+
(Actuals!A2:A630=5260211)+(Actuals!A2:A630=5400111)+
(Actuals!A2:A630=5400211)*(Actuals!A2:A630=5400311)+
(Actuals!A2:A630=5401211)*(Actuals!A2:A630=5440111)+
(Actuals!A2:A630=5480111)*(Actuals!A2:A630=5640111)+
(Actuals!A2:A630=5640211))*(Actuals!C2:C630))
 
M

Mike H

Caroline,

This requires a different approach. For debugging I shortened the range but
you can put that back. This is an array formula, see below on how to enter it

=SUM(IF(OR(Actuals!A1:A20=5010111,Actuals!A1:A20=5010211,Actuals!A1:A20=5120111,Actuals!A1:A20=5200111,Actuals!A1:A20=5200211,Actuals!A1:A20=5260111,Actuals!A1:A20=5260211,Actuals!A1:A20=5540111,Actuals!A1:A2=5400211,Actuals!A1:A20=5400311,Actuals!A1:A20=5401211,Actuals!A1:A2=5440111,Actuals!A1:A20=5480111,Actuals!A1:A20=5640111,Actuals!A1:A20=5640211),Actuals!C1:C20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike
 
J

Jacob Skaria

With the 15 numbers in J1:J15 you can try the below formula to sum the values
in C1:C20 for matching cells in A1:A20

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A20,J1:J15,0))),C1:C20)

If this post helps click Yes
 
T

Tom Hutchins

Try this

=SUMPRODUCT((Actuals!A2:A630={5010111,5010211,5120111,5200111,5200211,5260111,5260211,5400111,5400211,5400311,5401211,5440111,5480111,5640111,5640211})*(Actuals!C2:C630))

Or, if the codes are entered as text instead of number:

=SUMPRODUCT((Actuals!A2:A630={"5010111","5010211","5120111","5200111","5200211","5260111","5260211","5400111","5400211","5400311","5401211","5440111","5480111","5640111","5640211"})*(Actuals!C2:C630))

Your formula was looking for a cell that matched all the codes at once,
which is why it failed & returned 0.

Hope this helps,

Hutch
 
C

Caroline

Thank you to everyone who responded, my boss is going to be impressed. Also,
just realised that I'm on a United States site, and I'm in the UK, so if you
are all in the States, hello from over the pond!

Caroline
 
D

David Biddulph

You are not "on a United States site". You are in a newsgroup, which is
international.

Some of you may be accessing the newsgroup via various web portals, but the
newsgroup has contributors from many parts of the world, including those of
us in the UK.
 
B

Bob Phillips

Indeed. David and I are from the UK, I believe Mike H is also from the UK,
Jacob is in India, and Glenn I do not know. So very little if any US input.
 
M

Mike H

Correct Bob, Merseyside.

Bob Phillips said:
Indeed. David and I are from the UK, I believe Mike H is also from the UK,
Jacob is in India, and Glenn I do not know. So very little if any US input.

--
__________________________________
HTH

Bob




.
Click to expand...
 
G

Glenn

Bob said:
Indeed. David and I are from the UK, I believe Mike H is also from the UK,
Jacob is in India, and Glenn I do not know. So very little if any US input.
Click to expand...


I'm the "very little" input. ;-)
 
Top