DOH. Any Excel/ss wizards around? My Excel skillz are rudimentary so I'll try to explain as best I can.
My lovely spreadsheet would be perfect except for this one thing. I'm using SUMIF in an array to count based on a couple of things, but the text value I'm searching for isn't consistant. For example, I'm looking for any row with "Med" in column F, but I also want it to count any "Med-CMO", "OR-Med" and any others out there. Now in a simple formula no problem, just throw ="*Med*" and it'd be fine, but for some reason it doesn't work at all with my arrays.
{=SUM(IF((VendorName!$D$2:$D$252=1)*(VendorName!$F$2:$F$252="Med"),1,0))}
Ok, this works fine, except using "Med" as a criteria ignores the other, similar entries like Med-CMO. The "1" I'm searching for in D is an ordinal, the "Med" is the evaluators discipline, basically I'm searching for the number of times someone in the Med category (a doctor) gave this vendor the highest mark in our evaluation. I could just count them manually but there's hundreds of scores and I'd prefer to leave them with a nice worksheet they could easily edit to pull up any of the dozens of other disciplines they may want.
Can I not use wildcards at all? If I put *Med* it accepts it, but always returns a 0 as the result. Can I use OR or some other nested function in ="Med", like =OR("Med","Med-CMO"...)?
My lovely spreadsheet would be perfect except for this one thing. I'm using SUMIF in an array to count based on a couple of things, but the text value I'm searching for isn't consistant. For example, I'm looking for any row with "Med" in column F, but I also want it to count any "Med-CMO", "OR-Med" and any others out there. Now in a simple formula no problem, just throw ="*Med*" and it'd be fine, but for some reason it doesn't work at all with my arrays.
{=SUM(IF((VendorName!$D$2:$D$252=1)*(VendorName!$F$2:$F$252="Med"),1,0))}
Ok, this works fine, except using "Med" as a criteria ignores the other, similar entries like Med-CMO. The "1" I'm searching for in D is an ordinal, the "Med" is the evaluators discipline, basically I'm searching for the number of times someone in the Med category (a doctor) gave this vendor the highest mark in our evaluation. I could just count them manually but there's hundreds of scores and I'd prefer to leave them with a nice worksheet they could easily edit to pull up any of the dozens of other disciplines they may want.
Can I not use wildcards at all? If I put *Med* it accepts it, but always returns a 0 as the result. Can I use OR or some other nested function in ="Med", like =OR("Med","Med-CMO"...)?
