[Solved] Total cost combining index match and sumproduct when data is both vertical and horizontal

[rmonty92]

Please see below a simplified example of the spreadsheet I'm working on. This data will be on three separate tabs and I've got to keep the data in that format which is proving difficult with it effectively transposed.
I need to to calculate the total cost based on the day number, using the sum of each job id per day multiplied by their price rate. There will be a further variable as there will also be an individual column per customer on Revenue spreadsheet for total cost with individual tabs per customer but trying to master this before adding further variable. I've tried various combinations of SUMPRODUCT, IFS,SUMIFS, INDEX MATCH ETC (some examples in screenshot) but am either getting a returned #VALUE!, #N/A or total of all prices*the day number. (on the actual spreadsheet, the tab names! are inc. in the formula). Is this possible or am I just expecting too much?

I've managed to sum the number of jobs matching each job ID and day number. but can't extend this to calculate the total cost.

EDITED: =IF(M$2=$T3,SUMPRODUCT($M$3:M8,$K$3:$K$8),0), *RETURNS THE SUM * PRICE FOR EACH ROW, BUT NEED IT DYNAMICALLY TO INCLUDE ALL COLUMNS AS FORMULA NEEDS DRAGGING DOWN FOR 31 DAYS AND EVENTUALLY ACROSS MULTIPLE COLUMNS ONCE HAVE INDIVIDUAL CUSTOMER COLUMNS. I CAN ALSO GET IT TO SUM * PRICE USING (IF(AND() WHEN HAVE THE CUSTOMER TOTAL COST HEADER AND CUSTOMER NAME ON SERVICES TAB. CANNOT GET IT TO WORK WITHOUT SPECIFYING SPECIFIC COLUMNS AND MANUALLY AMENDING EACH TIME AND ONCE IT BECOMES POTENTIALLY 150 CELLS, WILL NEED A MORE EFFICIENT FORMULA#

 

[Nahian]

Please see below a simplified example of the spreadsheet I'm working on. This data will be on three separate tabs and I've got to keep the data in that format which is proving difficult with it effectively transposed.
I need to to calculate the total cost based on the day number, using the sum of each job id per day multiplied by their price rate. There will be a further variable as there will also be an individual column per customer on Revenue spreadsheet for total cost with individual tabs per customer but trying to master this before adding further variable. I've tried various combinations of SUMPRODUCT, IFS,SUMIFS, INDEX MATCH ETC (some examples in screenshot) but am either getting a returned #VALUE!, #N/A or total of all prices*the day number. (on the actual spreadsheet, the tab names! are inc. in the formula). Is this possible or am I just expecting too much?

I've managed to sum the number of jobs matching each job ID and day number. but can't extend this to calculate the total cost.

EDITED: =IF(M$2=$T3,SUMPRODUCT($M$3:M8,$K$3:$K$8),0), *RETURNS THE SUM * PRICE FOR EACH ROW, BUT NEED IT DYNAMICALLY TO INCLUDE ALL COLUMNS AS FORMULA NEEDS DRAGGING DOWN FOR 31 DAYS AND EVENTUALLY ACROSS MULTIPLE COLUMNS ONCE HAVE INDIVIDUAL CUSTOMER COLUMNS. I CAN ALSO GET IT TO SUM * PRICE USING (IF(AND() WHEN HAVE THE CUSTOMER TOTAL COST HEADER AND CUSTOMER NAME ON SERVICES TAB. CANNOT GET IT TO WORK WITHOUT SPECIFYING SPECIFIC COLUMNS AND MANUALLY AMENDING EACH TIME AND ONCE IT BECOMES POTENTIALLY 150 CELLS, WILL NEED A MORE EFFICIENT FORMULA#

Here is a formula that can sumproduct the values under days and job prices based on day numbers:
=IF(INDIRECT(CHAR(COLUMN($M$2)+63+ROW(A1))&ROW($M$2))=$T3,SUMPRODUCT(INDIRECT(CHAR(COLUMN($M$2)+63+ROW(A1))&3&":"&CHAR(COLUMN($M$2)+63+ROW(A1))&33),$K$3:$K$33),0)
As you have said you want to drag the formula for 31 days, I've made necessary changes for this in the formula (changed the row number 8 to 33, see at the end part of the formula). If you have any more clarification or want a complete solution of your workbook in a template form, please contact our Services page.

 

[rmonty92]

Here is a formula that can sumproduct the values under days and job prices based on day numbers:
=IF(INDIRECT(CHAR(COLUMN($M$2)+63+ROW(A1))&ROW($M$2))=$T3,SUMPRODUCT(INDIRECT(CHAR(COLUMN($M$2)+63+ROW(A1))&3&":"&CHAR(COLUMN($M$2)+63+ROW(A1))&33),$K$3:$K$33),0)
As you have said you want to drag the formula for 31 days, I've made necessary changes for this in the formula (changed the row number 8 to 33, see at the end part of the formula). If you have any more clarification or want a complete solution of your workbook in a template form, please contact our Services page.

Thank you. This worked on my sample spreadsheet screenshotted. Took a while to finally adapt to my actual spreadsheet where there are 3tabs and the ranges in slightly different rows etc but finally got it working =IF(INDIRECT("SERVICES!"&CHAR(COLUMN(SERVICES!$F$4)+63+ROW($A1))&ROW(SERVICES!$F$4))=REVENUE!$C5,SUMPRODUCT(INDIRECT("SERVICES!"&CHAR(COLUMN(SERVICES!$F$4)+63+ROW($A1))&5&":"&CHAR(COLUMN(SERVICES!$F$4)+63+ROW($A1))&25),SERVICES!$E$5:$E$25),0)

Now to figure out how to add in another variable to find the total cost per customer based on customer name next to the prices! Thanks again

 

[Nahian]

Thank you. This worked on my sample spreadsheet screenshotted. Took a while to finally adapt to my actual spreadsheet where there are 3tabs and the ranges in slightly different rows etc but finally got it working =IF(INDIRECT("SERVICES!"&CHAR(COLUMN(SERVICES!$F$4)+63+ROW($A1))&ROW(SERVICES!$F$4))=REVENUE!$C5,SUMPRODUCT(INDIRECT("SERVICES!"&CHAR(COLUMN(SERVICES!$F$4)+63+ROW($A1))&5&":"&CHAR(COLUMN(SERVICES!$F$4)+63+ROW($A1))&25),SERVICES!$E$5:$E$25),0)

Now to figure out how to add in another variable to find the total cost per customer based on customer name next to the prices! Thanks again

Here, I have used a similar formula based on your screenshot. I considered JOB IDs as customer names.
=IF(INDIRECT(CHAR(COLUMN($C$2)+63+ROW(A1))&ROW($C$2))=$J3,SUMPRODUCT(INDIRECT(CHAR(COLUMN($C$2)+63+ROW(A1))&3&":"&CHAR(COLUMN($C$2)+63+ROW(A1))&8),$K$3:$K$8),0)
I made a formula to sumproduct the quantity under each JOB ID and the JOB PRICE. See the image below for a better understanding. If you have any confusion, please let me know.