Need and combination in my existing formula.

[kishoremcp]

Hi,
I have the below formula which is working good.

Please Login or Register  to view this content.

Now my problem is, if I select "Overall" in sheet input which is in cell B2 it should calculate (US&C,EAMER and GAR). Actually these are the different conditions for which it is working fine individually.

Ex: If I select US&C in B2 it is giving the result and if EAMER is selected then it is calculating the required result and giving the output.
Sameway for GAR also it is working.

So, if i select Overall then it should calculate all (US&C+EAMER+GAR)..

Help please!!

If I select Overall in input sheet, then what changes i have to make in the above formula. I am attaching the sample file for reference. The formulas are there in sheet WCSAT from B2 to C33. Please help...

[benishiryo]

hi kishoremcp. selecting "Overall" means it's going to go through the list to find the word "Overall" like it did for US&C,EAMER or GAR. it will not add up all of them. i know you added a word "Overall" in the Raw Data. but it's going to fail your COUNTIF criteria because it has only 1 row & the rest of the cells are empty. i did an IF formula in front to say if B2 in "Input" is "Overall", it will exclude 1 criteria in your COUNTIFS. it will not check for any names in Region. otherwise, do your formula you provided. it's pretty lengthy, but it's basically just a little repeat of your formula. hope that works for you.

Please Login or Register  to view this content.

[kishoremcp]

Is there any simple way to do the thing.....Because there is another overall in C2

[benishiryo]

hmmm, i tried adding in the IF for B2 & C2 & made it into an array formula now. So if it reads "Overall", it's gonna count non blanks. You have to press CTRL + SHIFT + ENTER to confirm the formula

Please Login or Register  to view this content.

[kishoremcp]

can I put the above array in a macro. If so please hlep