Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > FAO Mike
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 183
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,420
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,240
Default 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))
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11,501
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,520
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,069
Default 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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 183
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,651
Default 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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,420
Default 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




Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11,501
Default 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




.

Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,240
Default 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. ;-)
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mike H - Help Tia Excel Worksheet Functions 11 December 17th 08 09:21 PM
Mike H - Help Tia Excel Worksheet Functions 0 December 15th 08 09:43 PM
F.A.O Mike H leerem Excel Discussion (Misc queries) 3 December 10th 08 01:20 PM
Help!!! MIKE H Wu Excel Discussion (Misc queries) 3 November 9th 08 05:41 PM
Mike Window Menu Missing Excel Discussion (Misc queries) 6 March 15th 05 03:49 PM


All times are GMT +1. The time now is 08:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement