Index/match with multiple match criteria

[USAOz]

I have a wages spreadsheet (sample attached) which has columns for Employee ID, quarter in which wages were earned and wages earned

I have a summary table below the date intoi which I wish to sum the total wages for a selected employee in a particular quarter.

My underrstanding of thye problem is that I wouyld need to index the wages , match the employee id in the sumary table AND the quarter in the summary table to total the employee's wages in each quarter.

I am using ther formula

=IF(ISBLANK($A27),0,IFERROR(INDEX($C$4:$C$16,MATCH($A27,$A$4:$A$16,0)),"")) but that not only does NOT correctly sum each employee for Quarter 1 but fails to sum Q2,3,and 4. The atached sample shows the Employee ID (column A), Earniong Quarter (Column B) and wages (Column C). Below that data is an incorrect summary table and a table showing my expected values. Any help is solving this? Thanks.

[alansidman]

The attached spreadsheet does not relate to the explanation you provided. Did you attach the wrong workbook?

[USAOz]

Hmmmmm - how can that happen? It was the only file on my desktop but, you ARE correct. I will try to upload the correct one again. Sorry

[USAOz]

OK, correct file now uploaded

[USAOz]

Have you had a chance to view the correct file?

[AliGW]

Your sample results data is all wrong - there is only one ID match!

This will return proper matches:

=INDEX('Employees & Contractors'!$C$3:$I$6,MATCH('Wages & Salaries'!$A4,'Employees & Contractors'!$B$3:$B$6,0),MATCH('Wages & Salaries'!B$3,'Employees & Contractors'!$C$2:$I$2,0))

If the results you show are what you want, you have a lot of explaining to do!

[p45cal]

Have you had a chance to view the correct file?

I see no difference between the files - perhaps you updated them both?
I don't see quarters data (no Earniong Quarter header), no date data and no "Below that data is an incorrect summary table and a table showing my expected values."

[USAOz]

I can see BOTH uploaded files on my emnd and they are definitely different! The correct file that matches my problem description is the latest upload and I will upload it again.

[USAOz]

OK, just a heads up, I did a tweak on the spreadsheet I just uploaded that should make things easier to understand so PLEASE view the attached REVISED spreadsheet. Sorry for the inconvenience. Basically, I still can't get INDEX/MATCH TO POPULATE THE SUMMARY TABLE on the Wages & Salaries worksheet

[Bob Phillips]

Here is a formula to give you weekly income for Q1, the rest should be easily figured out

=SUMPRODUCT(--(ROUNDUP(MONTH($C$8:$C$30)/3,0)=E$30),--($C$8:$C$30<>""),$L$8:$L$30)