If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sumproduct variables
Hello Group,
How do I include multiple cell reference variables in a sumproduct function? For example, if I wanted to include text values I would use - =Sumproduct((A1:A100={"yes","no","maybe"})....... This works but those values are variable and change often and would require a lot of editing. I could also use each value in a seperate array but that's not the way to go. But when I replace "yes" "no" "maybe" with cell refs I get the general formula error. I've tried these - {D1,E1,F1} {D1;E1;F1} Thanks for any help Tony |
#2
|
|||
|
|||
sumproduct variables
Tony
I got it to work by putting the yes, no, maybe in cells D1 - F1 and using =SUMPRODUCT((A1:A100=D1:F1)*1) Tony -----Original Message----- Hello Group, How do I include multiple cell reference variables in a sumproduct function? For example, if I wanted to include text values I would use - =Sumproduct((A1:A100={"yes","no","maybe"})..... .. This works but those values are variable and change often and would require a lot of editing. I could also use each value in a seperate array but that's not the way to go. But when I replace "yes" "no" "maybe" with cell refs I get the general formula error. I've tried these - {D1,E1,F1} {D1;E1;F1} Thanks for any help Tony . |
#3
|
|||
|
|||
sumproduct variables
-----Original Message----- Hello Group, How do I include multiple cell reference variables in a sumproduct function? For example, if I wanted to include text values I would use - =Sumproduct((A1:A100={"yes","no","maybe"})..... .. This works but those values are variable and change often and would require a lot of editing. I could also use each value in a seperate array but that's not the way to go. But when I replace "yes" "no" "maybe" with cell refs I get the general formula error. I've tried these - {D1,E1,F1} {D1;E1;F1} Thanks for any help Tony . In the example the cells are a contiguous range but what if they were not? Say they were D1,E5,F22. Tony |
#4
|
|||
|
|||
sumproduct variables
You could try: =SUMPRODUCT((A1:A100=D1)+(A1:A100=E5)+(A1:A100=F22 )) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tony" wrote in message ... -----Original Message----- Hello Group, How do I include multiple cell reference variables in a sumproduct function? For example, if I wanted to include text values I would use - =Sumproduct((A1:A100={"yes","no","maybe"})..... .. This works but those values are variable and change often and would require a lot of editing. I could also use each value in a seperate array but that's not the way to go. But when I replace "yes" "no" "maybe" with cell refs I get the general formula error. I've tried these - {D1,E1,F1} {D1;E1;F1} Thanks for any help Tony . In the example the cells are a contiguous range but what if they were not? Say they were D1,E5,F22. Tony |
#5
|
|||
|
|||
sumproduct variables
=SUMPRODUCT(--ISNUMBER(MATCH($A$1:$A$100,$D1:$F1,0)),...)
"Tony" wrote in message ... Hello Group, How do I include multiple cell reference variables in a sumproduct function? For example, if I wanted to include text values I would use - =Sumproduct((A1:A100={"yes","no","maybe"})....... This works but those values are variable and change often and would require a lot of editing. I could also use each value in a seperate array but that's not the way to go. But when I replace "yes" "no" "maybe" with cell refs I get the general formula error. I've tried these - {D1,E1,F1} {D1;E1;F1} Thanks for any help Tony |
#6
|
|||
|
|||
sumproduct variables
"RagDyer" wrote...
You could try: =SUMPRODUCT((A1:A100=D1)+(A1:A100=E5)+(A1:A100=F2 2)) ... As a perverse alternative if all cells were text, =SUMPRODUCT(--(A1:A100=T(OFFSET(D1:F22,{0,4,21},{0,1,2),1,1)))) The underlying point is that array constants can hold only numeric, text and error *constants*. They can't include expressions, e.g., 1+2 and "a"&"b", or cell references. Multiple area range references won't work in any arithmetic expressions. They're only useful in functions that accept 3D references plus INDEX and functions that require range arguments, e.g., CELL's 2nd arg. -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. |
#7
|
|||
|
|||
sumproduct variables
Thanks guys. I was expecting something different. Maybe it
can't be done the way I think. Tony -----Original Message----- Hello Group, How do I include multiple cell reference variables in a sumproduct function? For example, if I wanted to include text values I would use - =Sumproduct((A1:A100={"yes","no","maybe"})..... .. This works but those values are variable and change often and would require a lot of editing. I could also use each value in a seperate array but that's not the way to go. But when I replace "yes" "no" "maybe" with cell refs I get the general formula error. I've tried these - {D1,E1,F1} {D1;E1;F1} Thanks for any help Tony . |
#8
|
|||
|
|||
sumproduct variables
Huh?
"Tony" wrote in message ... Thanks guys. I was expecting something different. Maybe it can't be done the way I think. Tony -----Original Message----- Hello Group, How do I include multiple cell reference variables in a sumproduct function? For example, if I wanted to include text values I would use - =Sumproduct((A1:A100={"yes","no","maybe"})..... .. This works but those values are variable and change often and would require a lot of editing. I could also use each value in a seperate array but that's not the way to go. But when I replace "yes" "no" "maybe" with cell refs I get the general formula error. I've tried these - {D1,E1,F1} {D1;E1;F1} Thanks for any help Tony . |
Thread Tools | |
Display Modes | |
|
|