index & match formula - Urgent

[samantha]

Hi
I am hoping someone can help - if you look at the uploaded file, on the tab monthly summary. I am using a formula to sum up the total score of any given person in a particular month. but the formula I am using is not working - I am using excel 2013. what can be wrong with my formula?

also, I need a formula on the work exp tab to work the same as the monthly summary formula however, this time it has to add up "duration of contract" column G.

 

[NARAYANK991]

Hi Samantha ,

Your formula is : =AVERAGE(IF(I244:AB244<>0,I244:AB244,""))

In what way is this not working ? Can you explain what the result should be in a couple of cases ?

Narayan

 

[Hui]

In Monthly Summary!D3:
=SUMPRODUCT(('Site Appraisal'!$A$4:$A$250=$A3)*(MONTH('Site Appraisal'!$F$4:$F$250)=MONTH(D$2))*('Site Appraisal'!$AC$4:$AC$250))
Copy across and down

 

[Pavan.Sada.PS]

Hello Samantha,

I hope the below formula would work..

In Monthly Summary!D3:
=IFERROR(INDEX('Site Appraisal'!$AC$4:$AC$700,MATCH(TRIM('Monthly Summary'!$A3)&"|"&'Monthly Summary'!D$2,INDEX(TRIM('Site Appraisal'!$A$4:$A$700)&"|"&TEXT('Site Appraisal'!$E$4:$E$700,"MMMM"),),0)),"")

Control shift enter

Copy across and down

 

[samantha]

wow let me say - this platform is awesome, thank you for the response - its the best excel helper I subscribed to so far - you guys rock.

so I tried the formula's and Pavan's formula worked, however, for some strange reason it will not work in the August column on the monthly summary - can you help.

and if I can get a formula for the work summary - you okes will be the gods of excel

 

[samantha]

hi can anyone help me on the date formula I asked about previously/

 

[Hui]

Narayan and I both posted solutions that I am pretty sure work in all cases
If they don't please post the file with data that shows them not working