| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT problems...argh...
Hello, once again, I have to read 4 columns of info and count the records.
Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
try this...
=SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17), --(DropCode_Log!$C$10:$C$400=Totals!$A19), --(DropCode_Log!$D$10:$D$400=Totals!B$18), --(DropCode_Log!$G$10:$G$400="SIK")) -- HTH... Jim Thomlinson "Cita" wrote: Hello, once again, I have to read 4 columns of info and count the records. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
Well, you do have a comma missing before the second --, and also I
don't think you need the first --. Actually, you don't need the second -- if you change it to *, so try this: =SUMPRODUCT((DropCode_Log!$B$10:$B$400=Totals!$A$1 7)*(DropCode_Log!$C $10:*$C$400=Totals!$A19)*(DropCode_Log!$D$10:$D$40 0=Totals!B $18)*(DropCode_Log!*$G$10:$G$400="SIK")) Hope this helps. Pete On Sep 23, 3:54*pm, Cita wrote: Hello, once again, I have to read 4 columns of info and count the records.. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:*$C$400=Totals!$A19)*(DropCode_Log!$D$1 0:$D$400=Totals!B$18)--(DropCode_Log!*$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
I would have thought you wanted either
=SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17),--(DropCode_Log!$C$10:$C$400=Totals!$A19),--(DropCode_Log!$D$10:$D$400=Totals!B$18),--(DropCode_Log!$G$10:$G$400="SIK")) or =SUMPRODUCT((DropCode_Log!$B$10:$B$400=Totals!$A$1 7)*(DropCode_Log!$C$10:$C$400=Totals!$A19)*(DropCo de_Log!$D$10:$D$400=Totals!B$18)*(DropCode_Log!$G$ 10:$G$400="SIK")) You've separated the last 2 terms with --. You should separate either with * or with ,-- You didn't need the double unary minus in front of the first term when you used the * operator to combine it with the second term. Double unary minus is there to force an arithmetic operation to convert from Boolean to a number, but as you've got an arithmetic operation anyway you don't need double unary minus as well. You can get away with mixing and matching commas and multiplications, but you need one or the other, and your *(DropCode_Log!$D$10:$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK") is unlikely to be what you want. It would behave as *(DropCode_Log!$D$10:$D$400=Totals!B$18)+(DropCode _Log!$G$10:$G$400="SIK") -- David Biddulph "Cita" wrote in message ... Hello, once again, I have to read 4 columns of info and count the records. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
Thanks guys...but I had tried all these variations before I posted and I
still can't get them to work. Any other suggestions? Maybe a different combination of functions? Thanks. "David Biddulph" wrote: I would have thought you wanted either =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17),--(DropCode_Log!$C$10:$C$400=Totals!$A19),--(DropCode_Log!$D$10:$D$400=Totals!B$18),--(DropCode_Log!$G$10:$G$400="SIK")) or =SUMPRODUCT((DropCode_Log!$B$10:$B$400=Totals!$A$1 7)*(DropCode_Log!$C$10:$C$400=Totals!$A19)*(DropCo de_Log!$D$10:$D$400=Totals!B$18)*(DropCode_Log!$G$ 10:$G$400="SIK")) You've separated the last 2 terms with --. You should separate either with * or with ,-- You didn't need the double unary minus in front of the first term when you used the * operator to combine it with the second term. Double unary minus is there to force an arithmetic operation to convert from Boolean to a number, but as you've got an arithmetic operation anyway you don't need double unary minus as well. You can get away with mixing and matching commas and multiplications, but you need one or the other, and your *(DropCode_Log!$D$10:$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK") is unlikely to be what you want. It would behave as *(DropCode_Log!$D$10:$D$400=Totals!B$18)+(DropCode _Log!$G$10:$G$400="SIK") -- David Biddulph "Cita" wrote in message ... Hello, once again, I have to read 4 columns of info and count the records. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
If these don't work than you need to explain what does not work, if you get
zero when you expect something else than it is the data that is not what you think it is -- Regards, Peo Sjoblom "Cita" wrote in message ... Thanks guys...but I had tried all these variations before I posted and I still can't get them to work. Any other suggestions? Maybe a different combination of functions? Thanks. "David Biddulph" wrote: I would have thought you wanted either =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17),--(DropCode_Log!$C$10:$C$400=Totals!$A19),--(DropCode_Log!$D$10:$D$400=Totals!B$18),--(DropCode_Log!$G$10:$G$400="SIK")) or =SUMPRODUCT((DropCode_Log!$B$10:$B$400=Totals!$A$1 7)*(DropCode_Log!$C$10:$C$400=Totals!$A19)*(DropCo de_Log!$D$10:$D$400=Totals!B$18)*(DropCode_Log!$G$ 10:$G$400="SIK")) You've separated the last 2 terms with --. You should separate either with * or with ,-- You didn't need the double unary minus in front of the first term when you used the * operator to combine it with the second term. Double unary minus is there to force an arithmetic operation to convert from Boolean to a number, but as you've got an arithmetic operation anyway you don't need double unary minus as well. You can get away with mixing and matching commas and multiplications, but you need one or the other, and your *(DropCode_Log!$D$10:$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK") is unlikely to be what you want. It would behave as *(DropCode_Log!$D$10:$D$400=Totals!B$18)+(DropCode _Log!$G$10:$G$400="SIK") -- David Biddulph "Cita" wrote in message ... Hello, once again, I have to read 4 columns of info and count the records. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
It's not counting the last condition correctly with everything else i.e.
--(DropCode_Log!$G$10:$G$400="SIK")). I get 0's where I should have numbers. Also, I used the first part of this formula (without the "SIK" condition) as it is written and it works... "Peo Sjoblom" wrote: If these don't work than you need to explain what does not work, if you get zero when you expect something else than it is the data that is not what you think it is -- Regards, Peo Sjoblom "Cita" wrote in message ... Thanks guys...but I had tried all these variations before I posted and I still can't get them to work. Any other suggestions? Maybe a different combination of functions? Thanks. "David Biddulph" wrote: I would have thought you wanted either =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17),--(DropCode_Log!$C$10:$C$400=Totals!$A19),--(DropCode_Log!$D$10:$D$400=Totals!B$18),--(DropCode_Log!$G$10:$G$400="SIK")) or =SUMPRODUCT((DropCode_Log!$B$10:$B$400=Totals!$A$1 7)*(DropCode_Log!$C$10:$C$400=Totals!$A19)*(DropCo de_Log!$D$10:$D$400=Totals!B$18)*(DropCode_Log!$G$ 10:$G$400="SIK")) You've separated the last 2 terms with --. You should separate either with * or with ,-- You didn't need the double unary minus in front of the first term when you used the * operator to combine it with the second term. Double unary minus is there to force an arithmetic operation to convert from Boolean to a number, but as you've got an arithmetic operation anyway you don't need double unary minus as well. You can get away with mixing and matching commas and multiplications, but you need one or the other, and your *(DropCode_Log!$D$10:$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK") is unlikely to be what you want. It would behave as *(DropCode_Log!$D$10:$D$400=Totals!B$18)+(DropCode _Log!$G$10:$G$400="SIK") -- David Biddulph "Cita" wrote in message ... Hello, once again, I have to read 4 columns of info and count the records. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
Perhaps in your column G you have something else beyond the "SIK" (perhaps
spaces or other non-printing characters). Try a couple of helper columns =G10="SIK" should give TRUE if you really have "SIK", and =LEN(G10) should give 3 if you have "SIK". Another simple thing to overlook could be if you had not "SIK" but "S1K" or "SlK". -- David Biddulph "Cita" wrote in message ... It's not counting the last condition correctly with everything else i.e. --(DropCode_Log!$G$10:$G$400="SIK")). I get 0's where I should have numbers. Also, I used the first part of this formula (without the "SIK" condition) as it is written and it works... "Peo Sjoblom" wrote: If these don't work than you need to explain what does not work, if you get zero when you expect something else than it is the data that is not what you think it is -- Regards, Peo Sjoblom "Cita" wrote in message ... Thanks guys...but I had tried all these variations before I posted and I still can't get them to work. Any other suggestions? Maybe a different combination of functions? Thanks. "David Biddulph" wrote: I would have thought you wanted either =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17),--(DropCode_Log!$C$10:$C$400=Totals!$A19),--(DropCode_Log!$D$10:$D$400=Totals!B$18),--(DropCode_Log!$G$10:$G$400="SIK")) or =SUMPRODUCT((DropCode_Log!$B$10:$B$400=Totals!$A$1 7)*(DropCode_Log!$C$10:$C$400=Totals!$A19)*(DropCo de_Log!$D$10:$D$400=Totals!B$18)*(DropCode_Log!$G$ 10:$G$400="SIK")) You've separated the last 2 terms with --. You should separate either with * or with ,-- You didn't need the double unary minus in front of the first term when you used the * operator to combine it with the second term. Double unary minus is there to force an arithmetic operation to convert from Boolean to a number, but as you've got an arithmetic operation anyway you don't need double unary minus as well. You can get away with mixing and matching commas and multiplications, but you need one or the other, and your *(DropCode_Log!$D$10:$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK") is unlikely to be what you want. It would behave as *(DropCode_Log!$D$10:$D$400=Totals!B$18)+(DropCode _Log!$G$10:$G$400="SIK") -- David Biddulph "Cita" wrote in message ... Hello, once again, I have to read 4 columns of info and count the records. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
Thanks David. The "SIK" is part of a drop-down list...I'll try your
suggestion with the helper columns and LEN... "David Biddulph" wrote: Perhaps in your column G you have something else beyond the "SIK" (perhaps spaces or other non-printing characters). Try a couple of helper columns =G10="SIK" should give TRUE if you really have "SIK", and =LEN(G10) should give 3 if you have "SIK". Another simple thing to overlook could be if you had not "SIK" but "S1K" or "SlK". -- David Biddulph "Cita" wrote in message ... It's not counting the last condition correctly with everything else i.e. --(DropCode_Log!$G$10:$G$400="SIK")). I get 0's where I should have numbers. Also, I used the first part of this formula (without the "SIK" condition) as it is written and it works... "Peo Sjoblom" wrote: If these don't work than you need to explain what does not work, if you get zero when you expect something else than it is the data that is not what you think it is -- Regards, Peo Sjoblom "Cita" wrote in message ... Thanks guys...but I had tried all these variations before I posted and I still can't get them to work. Any other suggestions? Maybe a different combination of functions? Thanks. "David Biddulph" wrote: I would have thought you wanted either =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17),--(DropCode_Log!$C$10:$C$400=Totals!$A19),--(DropCode_Log!$D$10:$D$400=Totals!B$18),--(DropCode_Log!$G$10:$G$400="SIK")) or =SUMPRODUCT((DropCode_Log!$B$10:$B$400=Totals!$A$1 7)*(DropCode_Log!$C$10:$C$400=Totals!$A19)*(DropCo de_Log!$D$10:$D$400=Totals!B$18)*(DropCode_Log!$G$ 10:$G$400="SIK")) You've separated the last 2 terms with --. You should separate either with * or with ,-- You didn't need the double unary minus in front of the first term when you used the * operator to combine it with the second term. Double unary minus is there to force an arithmetic operation to convert from Boolean to a number, but as you've got an arithmetic operation anyway you don't need double unary minus as well. You can get away with mixing and matching commas and multiplications, but you need one or the other, and your *(DropCode_Log!$D$10:$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK") is unlikely to be what you want. It would behave as *(DropCode_Log!$D$10:$D$400=Totals!B$18)+(DropCode _Log!$G$10:$G$400="SIK") -- David Biddulph "Cita" wrote in message ... Hello, once again, I have to read 4 columns of info and count the records. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
No dice...
"Cita" wrote: Thanks David. The "SIK" is part of a drop-down list...I'll try your suggestion with the helper columns and LEN... "David Biddulph" wrote: Perhaps in your column G you have something else beyond the "SIK" (perhaps spaces or other non-printing characters). Try a couple of helper columns =G10="SIK" should give TRUE if you really have "SIK", and =LEN(G10) should give 3 if you have "SIK". Another simple thing to overlook could be if you had not "SIK" but "S1K" or "SlK". -- David Biddulph "Cita" wrote in message ... It's not counting the last condition correctly with everything else i.e. --(DropCode_Log!$G$10:$G$400="SIK")). I get 0's where I should have numbers. Also, I used the first part of this formula (without the "SIK" condition) as it is written and it works... "Peo Sjoblom" wrote: If these don't work than you need to explain what does not work, if you get zero when you expect something else than it is the data that is not what you think it is -- Regards, Peo Sjoblom "Cita" wrote in message ... Thanks guys...but I had tried all these variations before I posted and I still can't get them to work. Any other suggestions? Maybe a different combination of functions? Thanks. "David Biddulph" wrote: I would have thought you wanted either =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17),--(DropCode_Log!$C$10:$C$400=Totals!$A19),--(DropCode_Log!$D$10:$D$400=Totals!B$18),--(DropCode_Log!$G$10:$G$400="SIK")) or =SUMPRODUCT((DropCode_Log!$B$10:$B$400=Totals!$A$1 7)*(DropCode_Log!$C$10:$C$400=Totals!$A19)*(DropCo de_Log!$D$10:$D$400=Totals!B$18)*(DropCode_Log!$G$ 10:$G$400="SIK")) You've separated the last 2 terms with --. You should separate either with * or with ,-- You didn't need the double unary minus in front of the first term when you used the * operator to combine it with the second term. Double unary minus is there to force an arithmetic operation to convert from Boolean to a number, but as you've got an arithmetic operation anyway you don't need double unary minus as well. You can get away with mixing and matching commas and multiplications, but you need one or the other, and your *(DropCode_Log!$D$10:$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK") is unlikely to be what you want. It would behave as *(DropCode_Log!$D$10:$D$400=Totals!B$18)+(DropCode _Log!$G$10:$G$400="SIK") -- David Biddulph "Cita" wrote in message ... Hello, once again, I have to read 4 columns of info and count the records. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT problems...argh...
Congratulations on winning the gold medal for the most unhelpful reply of
the week. -- David Biddulph "Cita" wrote in message ... No dice... "Cita" wrote: Thanks David. The "SIK" is part of a drop-down list...I'll try your suggestion with the helper columns and LEN... "David Biddulph" wrote: Perhaps in your column G you have something else beyond the "SIK" (perhaps spaces or other non-printing characters). Try a couple of helper columns =G10="SIK" should give TRUE if you really have "SIK", and =LEN(G10) should give 3 if you have "SIK". Another simple thing to overlook could be if you had not "SIK" but "S1K" or "SlK". -- David Biddulph "Cita" wrote in message ... It's not counting the last condition correctly with everything else i.e. --(DropCode_Log!$G$10:$G$400="SIK")). I get 0's where I should have numbers. Also, I used the first part of this formula (without the "SIK" condition) as it is written and it works... "Peo Sjoblom" wrote: If these don't work than you need to explain what does not work, if you get zero when you expect something else than it is the data that is not what you think it is -- Regards, Peo Sjoblom "Cita" wrote in message ... Thanks guys...but I had tried all these variations before I posted and I still can't get them to work. Any other suggestions? Maybe a different combination of functions? Thanks. "David Biddulph" wrote: I would have thought you wanted either =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17),--(DropCode_Log!$C$10:$C$400=Totals!$A19),--(DropCode_Log!$D$10:$D$400=Totals!B$18),--(DropCode_Log!$G$10:$G$400="SIK")) or =SUMPRODUCT((DropCode_Log!$B$10:$B$400=Totals!$A$1 7)*(DropCode_Log!$C$10:$C$400=Totals!$A19)*(DropCo de_Log!$D$10:$D$400=Totals!B$18)*(DropCode_Log!$G$ 10:$G$400="SIK")) You've separated the last 2 terms with --. You should separate either with * or with ,-- You didn't need the double unary minus in front of the first term when you used the * operator to combine it with the second term. Double unary minus is there to force an arithmetic operation to convert from Boolean to a number, but as you've got an arithmetic operation anyway you don't need double unary minus as well. You can get away with mixing and matching commas and multiplications, but you need one or the other, and your *(DropCode_Log!$D$10:$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK") is unlikely to be what you want. It would behave as *(DropCode_Log!$D$10:$D$400=Totals!B$18)+(DropCode _Log!$G$10:$G$400="SIK") -- David Biddulph "Cita" wrote in message ... Hello, once again, I have to read 4 columns of info and count the records. Here's the formula: =SUMPRODUCT(--(DropCode_Log!$B$10:$B$400=Totals!$A$17)*(DropCode _Log!$C$10:$C$400=Totals!$A19)*(DropCode_Log!$D$10 :$D$400=Totals!B$18)--(DropCode_Log!$G$10:$G$400="SIK")) Where Column B are dates, Column C are categories, Column D is a 2nd category, and Column G are codes... What am I doing wrong? Thanks in advance... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I, too, am having problems with SUMPRODUCT | Excel Worksheet Functions | |||
| Sumproduct problems... | Excel Worksheet Functions | |||
| SUMPRODUCT problems | Excel Worksheet Functions | |||
| Problems with sumproduct | Excel Worksheet Functions | |||
| formula I can't figure it out?! ARGH | Excel Worksheet Functions | |||
Linear Mode
