Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Adjustment
=SUMPRODUCT(--($A$3:$A$3496="may")*($G$3:$G$3496="alexis")*($I$3 :$I$3496={"visit","pending","services" ,"agreement"}))
This formula works very well. However the 3rd part may (visit,pending,services,agreement) may change by month, either adding another word or taking off for example "services", or both. The 3rd part of the formula (visit,pending,services...) is in a list(another sheet), I just took those words an assigned a range tha I called "myrange". Q. Is it possible to insert "myrange" in this FORMULA in order to avoid writting all the words everytime I do the update? THANK YOU. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Adjustment
Say we have in A1 thru B10:
non-greek 1 alpha 1 beta 1 non-greek 1 non-greek 1 non-greek 1 non-greek 1 non-greek 1 non-greek 1 non-greek 1 Either formula: =SUMPRODUCT((A1:A10=qwerty)*(B1:B10=1)) =SUMPRODUCT((A1:A10={"alpha","beta","gamma","delta "})*(B1:B10=1)) will product the same result: 2 if qwerty is a Defined Name: ={"alpha","beta","gamma","delta"} The advantage to using the Defined Name is that only qwerty need be modified rather than every formula that uses qwerty. -- Gary''s Student - gsnu200851 "XLFanatico" wrote: =SUMPRODUCT(--($A$3:$A$3496="may")*($G$3:$G$3496="alexis")*($I$3 :$I$3496={"visit","pending","services" ,"agreement"})) This formula works very well. However the 3rd part may (visit,pending,services,agreement) may change by month, either adding another word or taking off for example "services", or both. The 3rd part of the formula (visit,pending,services...) is in a list(another sheet), I just took those words an assigned a range tha I called "myrange". Q. Is it possible to insert "myrange" in this FORMULA in order to avoid writting all the words everytime I do the update? THANK YOU. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Adjustment
In article ,
XLFanatico wrote: =SUMPRODUCT(--($A$3:$A$3496="may")*($G$3:$G$3496="alexis")*($I$3 :$I$3496={"vis it","pending","services" ,"agreement"})) This formula works very well. However the 3rd part may (visit,pending,services,agreement) may change by month, either adding another word or taking off for example "services", or both. The 3rd part of the formula (visit,pending,services...) is in a list(another sheet), I just took those words an assigned a range tha I called "myrange". Q. Is it possible to insert "myrange" in this FORMULA in order to avoid writting all the words everytime I do the update? THANK YOU. Try... =SUMPRODUCT(--($A$3:$A$3496="may"),--($G$3:$G$3496="alexis"),--ISNUMBER(M ATCH($I$3:$I$3496,MyRange,0))) -- Domenic http://www.xl-central.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Adjustment
Yes it works good.
Thanks. "Domenic" wrote: In article , XLFanatico wrote: =SUMPRODUCT(--($A$3:$A$3496="may")*($G$3:$G$3496="alexis")*($I$3 :$I$3496={"vis it","pending","services" ,"agreement"})) This formula works very well. However the 3rd part may (visit,pending,services,agreement) may change by month, either adding another word or taking off for example "services", or both. The 3rd part of the formula (visit,pending,services...) is in a list(another sheet), I just took those words an assigned a range tha I called "myrange". Q. Is it possible to insert "myrange" in this FORMULA in order to avoid writting all the words everytime I do the update? THANK YOU. Try... =SUMPRODUCT(--($A$3:$A$3496="may"),--($G$3:$G$3496="alexis"),--ISNUMBER(M ATCH($I$3:$I$3496,MyRange,0))) -- Domenic http://www.xl-central.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Adjustment
Your formula is good, but is me doing something that's apparently wrong.
"Gary''s Student" wrote: Say we have in A1 thru B10: non-greek 1 alpha 1 beta 1 non-greek 1 non-greek 1 non-greek 1 non-greek 1 non-greek 1 non-greek 1 non-greek 1 Either formula: =SUMPRODUCT((A1:A10=qwerty)*(B1:B10=1)) =SUMPRODUCT((A1:A10={"alpha","beta","gamma","delta "})*(B1:B10=1)) will product the same result: 2 if qwerty is a Defined Name: ={"alpha","beta","gamma","delta"} The advantage to using the Defined Name is that only qwerty need be modified rather than every formula that uses qwerty. -- Gary''s Student - gsnu200851 "XLFanatico" wrote: =SUMPRODUCT(--($A$3:$A$3496="may")*($G$3:$G$3496="alexis")*($I$3 :$I$3496={"visit","pending","services" ,"agreement"})) This formula works very well. However the 3rd part may (visit,pending,services,agreement) may change by month, either adding another word or taking off for example "services", or both. The 3rd part of the formula (visit,pending,services...) is in a list(another sheet), I just took those words an assigned a range tha I called "myrange". Q. Is it possible to insert "myrange" in this FORMULA in order to avoid writting all the words everytime I do the update? THANK YOU. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Auto-Adjustment | New Users to Excel | |||
Using Cell Data as adjustment to formula references | Excel Worksheet Functions | |||
Formula Adjustment? | Excel Worksheet Functions | |||
formula adjustment | Excel Worksheet Functions | |||
Formula Adjustment - Help | Excel Worksheet Functions |