FAO Mike
 

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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline

FAO Mike
 

You are doing an AND test instead of OR.

Try

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

--
__________________________________
HTH

Bob

"Caroline" wrote in message
...
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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline

FAO Mike
 

Caroline wrote:
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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline



=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))

FAO Mike
 

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=5 010211,Actuals!A1:A20=5120111,Actuals!A1:A20=52001 11,Actuals!A1:A20=5200211,Actuals!A1:A20=5260111,A ctuals!A1:A20=5260211,Actuals!A1:A20=5540111,Actua ls!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=5640 211),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



"Caroline" wrote:

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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline

FAO Mike
 

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
---------------
Jacob Skaria


"Caroline" wrote:

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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline

FAO Mike
 

Try this

=SUMPRODUCT((Actuals!A2:A630={5010111,5010211,5120 111,5200111,5200211,5260111,5260211,5400111,540021 1,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:C6 30))

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

"Caroline" wrote:

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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline

FAO Mike
 

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

"Caroline" wrote:

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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline

FAO Mike
 

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.
--
David Biddulph

Caroline wrote:
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

"Caroline" wrote:

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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline

FAO Mike
 

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

Caroline wrote:
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

"Caroline" wrote:

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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline

FAO Mike
 

Correct Bob, Merseyside.

"Bob Phillips" wrote:

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

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

Caroline wrote:
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

"Caroline" wrote:

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)*(Actual s!A2:A630=5260111)*(Actuals!A2:A630=5260211)*(Actu als!A2:A630=5400111)*(Actuals!A2:A630=5400211)*(Ac tuals!A2:A630=5400311)*(Actuals!A2:A630=5401211)*( Actuals!A2:A630=5440111)*(Actuals!A2:A630=5480111) *(Actuals!A2:A630=5640111)*(Actuals!A2:A630=564021 1)*(Actuals!C2:C630))

Thanks again
Caroline




.

FAO Mike
 

Bob Phillips wrote:
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.



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