Lookup based on 3 criteria, summing and requiring index function

[Gillman]

Hi there,

I have hit a slight snag...

On the datasheet I need to retrieve numbers from, I have months running down Column A and payroll numbers running down Column B and account codes running across row 2. The problem is I need it to sum based on column A, month and column B, payroll number (a payroll number may appear twice in any given month), and the account codes in row 2 only ever appear once.

On my main sheet, I require a lookup which uses the indirect function to reference the datasheet and retrieve based on these three variable criterion defined on my main sheet (for example month will change).

Month (datasheet column A) - I need to sum based on this,
Payroll number (datasheet column B) - I need to sum based on this and
Account code (datasheet row 2).

Is there a formula I could setup to do this for me? If any of this is not clear enough, please let me know.

I’d appreciate any help anyone is able to offer on this.

Cheers,
Darren

 

[Trevor G]

Darren can you look to show some sample data, I think you need a SumIF formula but jsut need to check it with some sample data.

 

[Gillman]

Solved: Lookup based on 3 criteria, summing and requiring index function

Thanks for your response, I appreciate it. I actually managed to find a formula which will do this for me:

=SUMPRODUCT((INDIRECT($A$3&"!$A$3:$A$11")=$D$4)*(INDIRECT($A$3&"!$B$3:$B$11")=A6)*(INDIRECT($A$3&"!$E$2:$H$2")=B6)*(INDIRECT($A$3&"!$E$3:$H$11")))

Thanks again.