| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONCATENATE within SUMPRODUCT
I have a formula in Excel 2003:
=SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858)) Which returns the number of rows in my worksheet range where the value in column "E" equals the value in cell "A1858" AND the value in column "F" equals the value in cell "B1858". This formula is working well, but not 100% of what I need. I'd like to declare the "A1858" and "B1858" portions of the function on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW()) instead. So the resulting formula would be: =SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",ROW()))) However, when I do this, I get a result of 0, which is wrong. Can anyone please help me debug this error? Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE within SUMPRODUCT
=SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&ROW()))))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Icarus" wrote in message ... I have a formula in Excel 2003: =SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858)) Which returns the number of rows in my worksheet range where the value in column "E" equals the value in cell "A1858" AND the value in column "F" equals the value in cell "B1858". This formula is working well, but not 100% of what I need. I'd like to declare the "A1858" and "B1858" portions of the function on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW()) instead. So the resulting formula would be: =SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",ROW()))) However, when I do this, I get a result of 0, which is wrong. Can anyone please help me debug this error? Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE within SUMPRODUCT
Thank you. You Rock!
What is the "T" function doing? On Jul 11, 11:12*am, "Bob Phillips" wrote: =SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W())))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Icarus" wrote in message ... I have a formula in Excel 2003: =SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858)) Which returns the number of rows in my worksheet range where the value in column "E" equals the value in cell "A1858" AND the value in column "F" equals the value in cell "B1858". *This formula is working well, but not 100% of what I need. I'd like to declare the "A1858" and "B1858" portions of the function on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW()) instead. So the resulting formula would be: =SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW()))) However, when I do this, I get a result of 0, which is wrong. Can anyone please help me debug this error? Thank you.- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE within SUMPRODUCT
The "T" is for Text.
If you had numeric data in the columns, you'd need an "N" instead. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Icarus" wrote in message ... Thank you. You Rock! What is the "T" function doing? On Jul 11, 11:12 am, "Bob Phillips" wrote: =SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W())))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Icarus" wrote in message ... I have a formula in Excel 2003: =SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858)) Which returns the number of rows in my worksheet range where the value in column "E" equals the value in cell "A1858" AND the value in column "F" equals the value in cell "B1858". This formula is working well, but not 100% of what I need. I'd like to declare the "A1858" and "B1858" portions of the function on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW()) instead. So the resulting formula would be: =SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW()))) However, when I do this, I get a result of 0, which is wrong. Can anyone please help me debug this error? Thank you.- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE within SUMPRODUCT
If you had *mixed* data in your columns (text and numbers in *same* column),
you could try this: =SUMPRODUCT((E10:E1740=INDEX($A$1:$A$10000,ROW())) *(F10:F1740=INDEX($B$1:$B$10000,ROW()))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... The "T" is for Text. If you had numeric data in the columns, you'd need an "N" instead. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Icarus" wrote in message ... Thank you. You Rock! What is the "T" function doing? On Jul 11, 11:12 am, "Bob Phillips" wrote: =SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W())))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Icarus" wrote in message ... I have a formula in Excel 2003: =SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858)) Which returns the number of rows in my worksheet range where the value in column "E" equals the value in cell "A1858" AND the value in column "F" equals the value in cell "B1858". This formula is working well, but not 100% of what I need. I'd like to declare the "A1858" and "B1858" portions of the function on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW()) instead. So the resulting formula would be: =SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW()))) However, when I do this, I get a result of 0, which is wrong. Can anyone please help me debug this error? Thank you.- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE within SUMPRODUCT
Plus it is not volatile so I think it is preferable
-- Regards, Peo Sjoblom "RagDyeR" wrote in message ... If you had *mixed* data in your columns (text and numbers in *same* column), you could try this: =SUMPRODUCT((E10:E1740=INDEX($A$1:$A$10000,ROW())) *(F10:F1740=INDEX($B$1:$B$10000,ROW()))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... The "T" is for Text. If you had numeric data in the columns, you'd need an "N" instead. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Icarus" wrote in message ... Thank you. You Rock! What is the "T" function doing? On Jul 11, 11:12 am, "Bob Phillips" wrote: =SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W())))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Icarus" wrote in message ... I have a formula in Excel 2003: =SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858)) Which returns the number of rows in my worksheet range where the value in column "E" equals the value in cell "A1858" AND the value in column "F" equals the value in cell "B1858". This formula is working well, but not 100% of what I need. I'd like to declare the "A1858" and "B1858" portions of the function on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW()) instead. So the resulting formula would be: =SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW()))) However, when I do this, I get a result of 0, which is wrong. Can anyone please help me debug this error? Thank you.- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE within SUMPRODUCT
I thought of that as well, but much as I dislike INDIRECT, I didn't like the
idea of another lookup. I think that was probably a wrong call on my part (as does Peo <g), but my formula didn't restrict that lookup range =SUMPRODUCT((E10:E1740=INDEX($A:$A,ROW()))*(F10:F1 740=INDEX($B:$B,ROW()))) which I think is a tad better -- __________________________________ HTH Bob "RagDyeR" wrote in message ... If you had *mixed* data in your columns (text and numbers in *same* column), you could try this: =SUMPRODUCT((E10:E1740=INDEX($A$1:$A$10000,ROW())) *(F10:F1740=INDEX($B$1:$B$10000,ROW()))) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... The "T" is for Text. If you had numeric data in the columns, you'd need an "N" instead. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Icarus" wrote in message ... Thank you. You Rock! What is the "T" function doing? On Jul 11, 11:12 am, "Bob Phillips" wrote: =SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W())))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Icarus" wrote in message ... I have a formula in Excel 2003: =SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858)) Which returns the number of rows in my worksheet range where the value in column "E" equals the value in cell "A1858" AND the value in column "F" equals the value in cell "B1858". This formula is working well, but not 100% of what I need. I'd like to declare the "A1858" and "B1858" portions of the function on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW()) instead. So the resulting formula would be: =SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW()))) However, when I do this, I get a result of 0, which is wrong. Can anyone please help me debug this error? Thank you.- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE within SUMPRODUCT
It was introduced to reduce the single cell array that
INDIRECT("A"&(ROW())) creates, but as you point out, it only allows text values, and I have no idea where I got the idea it was text values. I would have been better to use INDIRECT("A"&MIN(ROW())) if using this form at all. -- __________________________________ HTH Bob "RagDyeR" wrote in message ... The "T" is for Text. If you had numeric data in the columns, you'd need an "N" instead. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Icarus" wrote in message ... Thank you. You Rock! What is the "T" function doing? On Jul 11, 11:12 am, "Bob Phillips" wrote: =SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO*W())))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Icarus" wrote in message ... I have a formula in Excel 2003: =SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858)) Which returns the number of rows in my worksheet range where the value in column "E" equals the value in cell "A1858" AND the value in column "F" equals the value in cell "B1858". This formula is working well, but not 100% of what I need. I'd like to declare the "A1858" and "B1858" portions of the function on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW()) instead. So the resulting formula would be: =SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R*OW()))) However, when I do this, I get a result of 0, which is wrong. Can anyone please help me debug this error? Thank you.- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE within SUMPRODUCT
It must have been a good guess, since you received a positive feed-back from
the OP.<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bob Phillips" wrote in message ... It was introduced to reduce the single cell array that INDIRECT("A"&(ROW())) creates, but as you point out, it only allows text values, and I have no idea where I got the idea it was text values. I would have been better to use INDIRECT("A"&MIN(ROW())) if using this form at all. -- __________________________________ HTH Bob "RagDyeR" wrote in message ... The "T" is for Text. If you had numeric data in the columns, you'd need an "N" instead. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Icarus" wrote in message ... Thank you. You Rock! What is the "T" function doing? On Jul 11, 11:12 am, "Bob Phillips" wrote: =SUMPRODUCT((E10:E1740=T(INDIRECT("A"&ROW())))*(F1 0:F1740=T(INDIRECT("B"&RO-W())))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Icarus" wrote in message ... I have a formula in Excel 2003: =SUMPRODUCT((E10:E1740=A1858)*(F10:F1740=B1858)) Which returns the number of rows in my worksheet range where the value in column "E" equals the value in cell "A1858" AND the value in column "F" equals the value in cell "B1858". This formula is working well, but not 100% of what I need. I'd like to declare the "A1858" and "B1858" portions of the function on the fly, using CONCATENATE("A",ROW()) and CONCATENATE("B",ROW()) instead. So the resulting formula would be: =SUMPRODUCT((E10:E1740=CONCATENATE("A",ROW()))*(F1 0:F1740=CONCATENATE("B",R-OW()))) However, when I do this, I get a result of 0, which is wrong. Can anyone please help me debug this error? Thank you.- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CONCATENATE within SUMPRODUCT
"Bob Phillips" wrote...
It was introduced to reduce the single cell array that INDIRECT("A"&(ROW())) .... Ah, the tribulations of the syntactically deprived! INDIRECT("RC1",0) and just the one volatile function call. HAND |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Concatenate | Excel Discussion (Misc queries) | |||
| CONCATENATE and SUMPRODUCT in a formula?? | Excel Discussion (Misc queries) | |||
| Concatenate | Excel Discussion (Misc queries) | |||
| I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
| concatenate | Excel Worksheet Functions | |||
Hybrid Mode
