sum with counta & countif with many matching criteria

Hello,

I have a problem with placing a formula in b24 so I can copy it accross and down.

I have tried to do combination of sums and counta's and countif's (only to minus N/A) in the range of index / match.

What I want to achive is:

in Actual section - I need to sum all percentages within the week required (in the Actual column in the top table) and then devide it by the number of cells that does not contain N/A.

in 5 days section - I need to sum all percentages within the range of Monday to Friday (in the top table) and devide it by 5

in 7 days section - I need to sum all percentages within the week Monday to Sunday (in the top table) and devide it by 7.

I do not know if I was using the right combination and if I actually used the right functions.

If anybody has any idea on how I can resolve this problem it would be great to share that knowledge.

Thank you Guys

Simon

Hello Simon,

See the attached.

I'm assuming this is only a simple example and that your real world workbook will have weeks which continue down the page. In which case you'll need to move the current analysis and helper cells out of the way and adjust the helper row formulae in row 20 as appropriate.

If you have the opportunity to change your layout I'd be inclined to do that so that you can simplify the formulae.

I'd suggest 5 columns for "Week no", "Day", "Actual/Capacity", "Happy/Doc" and "Percentage"

and then make each row a record like

35, 1, Actual, Happy, 95%
36, 4, Capacity, Doc, 79%

You can then use SUMPRODUCT() function or if you have Excel 2007 the SUMIFS() function.

HTH

Thank you for your suggestion,


I have changed the lay-out a little bit and then retyped the formula. What I had to do was to make sure that in Index(,Match,Match) I put them in right order - first up and down reference and then across reference. I also had to unmerge the cells so I could use Sum, Counta and Countif (index:index) with +as many cells as needed to be counted/summed in match point of the formula.

Please see the attached example for the final working result if anybody is interested how it has been resolved.

Best Regards
Simon