Offset and sumif combined formula?

Hello all, I need some help!

Please see the sample attached.

I am looking for assistance writing a formula (that site in Sheet 2 A2 etc) that looks down column A and finds all the data under "Resource Clocked 1" - In Sheet 1

Then it looks at the data that is only under that heading and analyses a letter code in column C and then sums up a total for each letter code from column E.

On sheet 2 I have the output table of what I am trying to achieve and have manually entered the results.

I'm making it sound more difficult than it is I think but cant explain it very well, looking at the attached spread sheet and what I have coloured in red and what I am expecting will make it easier to understand.

Thanks in advance

BVG

You can use this formula in B2:

=IFERROR(SUMIFS(INDEX(Sheet1!$E:$E,MATCH($A2,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$E:$E,MATCH("ed"&MID($A2,FIND(":",$A2)+1,4),Sheet1!$C:$C,0)-1),
INDEX(Sheet1!$C:$C,MATCH($A2,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$C:$C,MATCH("ed"&MID($A2,FIND(":",$A2)+1,4),Sheet1!$C:$C,0)-1),B$1),0)

then copy across and down as required.

Note that I have used INDEX, rather than the volatile OFFSET function, to generate the necessary ranges, and the formula returns zero if the Resource Clocked is not present.

Hope this helps.

Pete

Originally Posted by Pete_UK

You can use this formula in B2:

=IFERROR(SUMIFS(INDEX(Sheet1!$E:$E,MATCH($A2,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$E:$E,MATCH("ed"&MID($A2,FIND(":",$A2)+1,4),Sheet1!$C:$C,0)-1),
INDEX(Sheet1!$C:$C,MATCH($A2,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$C:$C,MATCH("ed"&MID($A2,FIND(":",$A2)+1,4),Sheet1!$C:$C,0)-1),B$1),0)

then copy across and down as required.

Note that I have used INDEX, rather than the volatile OFFSET function, to generate the necessary ranges, and the formula returns zero if the Resource Clocked is not present.

Hope this helps.

Pete

Worked perfectly, thank you so much. Way beyond my skill level!

Glad to help - thanks for the rep.

Pete