How to use CountIF and VlookUps properly.

[dmejia174]

Hello,

I have almost been able to finish this project. Please help ! On my attachment, there is 2 sheets. Stores and TAB. The stores sheet has all the data....but in the TAB sheet, i am hoping to get help on the correct formulas to enter into Table 3 which starts on cell C31. if the goal has been set at 95% on D31 which formula can i enter so that it can tell me which region names have 95% or better on column O - in the STORES sheet. and on cell to calculate how much over did it go beyond 95%. Then to count the number of stores achieving the 95% threshold...based off of column O. Please help!

Thank u for your time.

[:) Sixthsense :)]

Do you want to get the count alone or the names too?

[dmejia174]

Hello sixth sense. Sorry for confusion. Yes, how can the region name populate for those that have 95% or better- based off column O in stores sheet. And how can i also get count on #of stores that received 95% or better?

[:) Sixthsense :)]

In C31 Cell - Array Formula

Please Login or Register  to view this content.

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

For getting the count

Please Login or Register  to view this content.

[dmejia174]

Hello Sixth Sense, thank you!
Is it possible to know what formula i can enter in cell F31 so that i can get a count of all stores that has 95% or better for that specific region in cell C31? I entered your formula but the count came out to only 2 stores.

On cell G31 - is there way to know the percentage of stores that have achieved 95% or better?

Please let me know if you can. much appreciated!

[:) Sixthsense :)]

For getting the count of stores which is achieving the 95%

Please Login or Register  to view this content.

In G31 Cell - To get the average of stores matching the 95% - Format the cell as %

Please Login or Register  to view this content.

In the suggested Array formula just replace the = with >= and drag it down to get the region of stores.

[dmejia174]

On this formula - =COUNTIF(Store!O8:O646,">="&D31) this will give me all stores that has 95% or better....But how can i get -"how many stores have >=D31 and also the region name?

for example when i enter the array formula in C31 i get "Southwest Open R1" - so how can i get all the stores in that region that achieved 95% or better?

[:) Sixthsense :)]

Please make use of filter with conditions OR Pivot Table by sorting data in ascending or descending.

Don't give too much of pressure to formula's which is not suggestible according to my view.

[dmejia174]

thank you very much!