• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

index & match formula - Urgent

samantha

New Member
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.
 

Attachments

  • appraisal control sheet.xlsx
    442.8 KB · Views: 13
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
 
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
 
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
 
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 :)
 
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
 
Back
Top