INDEX and MATCH(?) combined with SUM across multiple sheets

Hi all,

I currently have 3 worksheets (please see attached):

- Contracted Value
- Type A Contracted
- Type B Contracted

'Type A Contracted' shows the chargeable unit split into columns of Incoming A, Outgoing A, Total A for the length of the contract. This is the same format used for the 'Type B Contracted'

The 'Contracted Value' tab shows the price per unit.

I would like to automate the 'Contracted Value' tab to update the 'Total Value' and value per month automatically based on the Region & ID Number and the SUM of the 'Total A' and 'Total B', from their respective Worksheets

I think INDEX and MATCH are the way forward, but I'm not sure how to combine a SUM function in there.

I've pasted in the Monthly Total and Total Value 'as values' as I'm doing this manually at the moment! I hope my explanation above is clear for the reader? Any feedback is greatly appreciated.

Please see attached file.

Regards
jusmightbeokay

Try this, I put in a sumif statement in October data on the first sheet, see if it does what you want

Thanks jprlimey, this useful information!

Do you know how could I apply this to the rest of the columns without lots of manual manipulation?

jusmightbeokay

I added a further feature to the formula that essentially offset the columns to sum. In your case on the 2nd and 3rd sheet your columns to sum are F,I,L .....

=SUMIFS((OFFSET('Type A Contracted'!F$3:F$27,0,2*(COLUMN(A$2)-1))),'Type A Contracted'!$A$3:$A$27,'Contracted Value'!$A2,'Type A Contracted'!$B$3:$B$27,'Contracted Value'!$B2)+SUMIFS((OFFSET('Type B Contracted'!F$3:F$84,0,2*(COLUMN(A$2)-1))),'Type B Contracted'!$A$3:$A$84,'Contracted Value'!$A2,'Type B Contracted'!$B$3:$B$84,'Contracted Value'!$B2)

Take a look and see if this does what you want