|
Can I use an array formula with multiple criteria in the same row?
I could use an "array formula" suggestion on my problem..........
I've created a database in Excel, and I am trying to use the array formula below to count for me, but it is only working if I use a single search criteria in Row AN (e.g. AC Initiated or Referred to Tx) but not both. What I'd like to do (as the formula is attempting to execute) is count all client's who were seen during the 2007 calendar year, and identify those 2007 client's who were "referred to tx" and those who began tx (e.g. AC Initiated). I didn't know if an Array formula can search for multiple criteria in the same row twice (once for all 2007 clients who were "Referred to Tx" and then again for all who initiated tx (AC Initiated). I've tried several variations of the formula below with no success. Any suggestions? Thank you in advance, Dan The Formula I was using that isn't working is: =SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="AC Initiated")*(AN4:AN3500="Referred to Tx")) |
Can I use an array formula with multiple criteria in the same row?
Try this sumproduct alternative, normal ENTER will do:
=SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(A N4:AN3500,{"AC Initiated";"Referred to Tx"},0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dan the Man" wrote: I could use an "array formula" suggestion on my problem.......... I've created a database in Excel, and I am trying to use the array formula below to count for me, but it is only working if I use a single search criteria in Row AN (e.g. AC Initiated or Referred to Tx) but not both. What I'd like to do (as the formula is attempting to execute) is count all client's who were seen during the 2007 calendar year, and identify those 2007 client's who were "referred to tx" and those who began tx (e.g. AC Initiated). I didn't know if an Array formula can search for multiple criteria in the same row twice (once for all 2007 clients who were "Referred to Tx" and then again for all who initiated tx (AC Initiated). I've tried several variations of the formula below with no success. Any suggestions? Thank you in advance, Dan The Formula I was using that isn't working is: =SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="AC Initiated")*(AN4:AN3500="Referred to Tx")) |
Can I use an array formula with multiple criteria in the same
Thank you Max. The sumproduct alternative also works with "normal entry" as
you suggested. Sorry for the multiple posts, but I'm a "newbie" to using this wonderful discussion group. Got another question to throw out at you or Ron (both who offered workable suggestions with normal entry or Array's). I attempted to modify another formula (using the "normal entry" suggestion from Max)without success. This formula has multiple search criteria (4). Here is that formula: =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4:S3500,{"RF Hearing & Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk Hearing"},0))) Thank you in advance! Dan "Max" wrote: Try this sumproduct alternative, normal ENTER will do: =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(A N4:AN3500,{"AC Initiated";"Referred to Tx"},0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dan the Man" wrote: I could use an "array formula" suggestion on my problem.......... I've created a database in Excel, and I am trying to use the array formula below to count for me, but it is only working if I use a single search criteria in Row AN (e.g. AC Initiated or Referred to Tx) but not both. What I'd like to do (as the formula is attempting to execute) is count all client's who were seen during the 2007 calendar year, and identify those 2007 client's who were "referred to tx" and those who began tx (e.g. AC Initiated). I didn't know if an Array formula can search for multiple criteria in the same row twice (once for all 2007 clients who were "Referred to Tx" and then again for all who initiated tx (AC Initiated). I've tried several variations of the formula below with no success. Any suggestions? Thank you in advance, Dan The Formula I was using that isn't working is: =SUM((--(YEAR(Z4:Z3500)=2007))*(AN4:AN3500="AC Initiated")*(AN4:AN3500="Referred to Tx")) |
Can I use an array formula with multiple criteria in the same
On Sun, 1 Jul 2007 09:02:04 -0700, Dan the Man
wrote: Thank you Max. The sumproduct alternative also works with "normal entry" as you suggested. Sorry for the multiple posts, but I'm a "newbie" to using this wonderful discussion group. Got another question to throw out at you or Ron (both who offered workable suggestions with normal entry or Array's). I attempted to modify another formula (using the "normal entry" suggestion from Max)without success. This formula has multiple search criteria (4). Here is that formula: =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH( S4:S3500,{"RF Hearing & Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk Hearing"},0))) Thank you in advance! Dan Try this **array-entered** =SUM((YEAR(Z4:Z3500)=2007)*(S4:S3500={"RF Hearing & Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client","Medical Risk Hearing"})) --ron |
Can I use an array formula with multiple criteria in the same
And the "array" response works.............Now for the "non array" response
from Max.....(lol). Best, Dan "Ron Rosenfeld" wrote: On Sun, 1 Jul 2007 09:02:04 -0700, Dan the Man wrote: Thank you Max. The sumproduct alternative also works with "normal entry" as you suggested. Sorry for the multiple posts, but I'm a "newbie" to using this wonderful discussion group. Got another question to throw out at you or Ron (both who offered workable suggestions with normal entry or Array's). I attempted to modify another formula (using the "normal entry" suggestion from Max)without success. This formula has multiple search criteria (4). Here is that formula: =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH( S4:S3500,{"RF Hearing & Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk Hearing"},0))) Thank you in advance! Dan Try this **array-entered** =SUM((YEAR(Z4:Z3500)=2007)*(S4:S3500={"RF Hearing & Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client","Medical Risk Hearing"})) --ron |
Can I use an array formula with multiple criteria in the same
The prob was you had a mixture of commas and semicolons separators in your
earlier: =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4:S3500,{"RF Hearing & Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk Hearing"},0))) Just correct all the "comma" separators between the various text phrases within that array to semicolons (;), viz this rendition should work fine: =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4:S3500,{"RF Hearing & Red. Fee";"MR Hearing & Red. Fee";"Red Flag Hearing Client";"Medical Risk Hearing"},0))) If you had all your text phrases listed in say, AX1 down, you could also use either this simpler entire cols version (the "AX:AX" part) (but it'll take a while to recalc): =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4:S3500,AX:AX,0))) Or, this much faster, defined range version (the "AX$1:AX$10" part): =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4:S3500,AX$1:AX$10,0))) where the text phrases would be listed within AX1:AX10 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dan the Man" wrote: .. Now for the "non array" response from Max ..... |
Can I use an array formula with multiple criteria in the same
Max and Ron are great..............Dan
"Max" wrote: The prob was you had a mixture of commas and semicolons separators in your earlier: =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4:S3500,{"RF Hearing & Red. Fee","MR Hearing & Red. Fee","Red Flag Hearing Client";"Medical Risk Hearing"},0))) Just correct all the "comma" separators between the various text phrases within that array to semicolons (;), viz this rendition should work fine: =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4:S3500,{"RF Hearing & Red. Fee";"MR Hearing & Red. Fee";"Red Flag Hearing Client";"Medical Risk Hearing"},0))) If you had all your text phrases listed in say, AX1 down, you could also use either this simpler entire cols version (the "AX:AX" part) (but it'll take a while to recalc): =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4:S3500,AX:AX,0))) Or, this much faster, defined range version (the "AX$1:AX$10" part): =SUMPRODUCT((YEAR(Z4:Z3500)=2007)*ISNUMBER(MATCH(S 4:S3500,AX$1:AX$10,0))) where the text phrases would be listed within AX1:AX10 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dan the Man" wrote: .. Now for the "non array" response from Max ..... |
Can I use an array formula with multiple criteria in the same
welcome, Dan.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dan the Man" wrote in message ... Max and Ron are great..............Dan |
| All times are GMT +1. The time now is 06:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com