Hello, it's my first time posting so I hope this is alright. I'm trying to get the sum from a table, of all the numbers in multiple columns (separated by columns with unnecessary information), also based on an IF check for a person's name in a column on the left. I'll post my formula below. It isn't working, could anyone help me understand why? Or suggest any better alternative? The range will become larger every week, so I want a formula that's not too long.
What I was trying to do, was have it do a check on the names in one column, and then do a second check for the word "Amt" in the row with the column headers, and have it sum all of the cells under each Amt column for each name. I hope I'm explaining this adequately.
=SUM('2018 WK1-26'!$G:$DG*('2018 WK1-26'!$B:$B=Summary!$B16)*('2018 WK1-26'!$D$7:$DG$7="Amt")), CSE
PS referencing the whole columns is not quite effective
Ok, so I tried the formula you gave me, but it's returning 0 for the total. I'm relatively new to Excel so could you explain what the * marks are in this formula? It's not multiplying anything is it?
Also, what would be a better option than referencing the whole column? is that just for the checks, or for the sum range as well?
Hello Ruuroga and Welcome to Excel Forum.
In the attached sample file the formula used is: =SUMPRODUCT(('2018 Wk 1-26'!B2:B8=B2)*('2018 Wk 1-26'!C1:H1="Amt")*('2018 Wk 1-26'!C2:H8))
If that were translated to your file it might read: =SUMPRODUCT(('2018 Wk 1-26'!B:B=B16)*('2018 Wk 1-26'!D7:DG7="Amt")*('2018 Wk 1-26'!D:DG))
If that doesn't work for your file, please upload a small desensitized sample by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
You're Welcome and thank you for the generous feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
Bookmarks