Forum Discussion
Using INDEX with SUMIFS?
=SUM(IF(INDEX(A:A,2):INDEX(A:A,ROW(A2))=F2,INDEX(B:B,2):INDEX(B:B,ROW(A2))))
Does this return the intended result? For illustration i've made an example within one worksheet but the formula can be adjusted to work across two worksheets. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
OliverScheurich I think something like this could work if I finalized it a little more. How would you go about adjusting this to work across two worksheets? Also, the code names appear many times across the rows in the datasheet where I want the point values summed. Would I need to do the =F portion of this equation for every column they appear in?
=SUM(IF(INDEX('Code Sheet'!A:A,2):INDEX('Code Sheet'!A:A,ROW('Code Sheet'!A2))=A2,INDEX('Code Sheet'!B:B,2):INDEX('Code Sheet'!B:B,ROW('Code Sheet'!A2))))
This formula works across both sheets in the attached sample file. The formula has to be entered with ctrl+shift+enter in Excel versions before Office 365 or Excel for the web.
Can you attach a screenshot without sensitive data which shows how the code names appear many times across the rows in the datasheet where you want the point values summed along with the expected result? I'm unsure how the database is set up. You could as well enter sample data in the sample file and attach the file to your reply.
OliverScheurich I have attached an example from my code sheet w corresponding code values and an example of how these codes appear in the other sheet, "2022" Each row in the "2022" sheet is an instance of a health inspection at a restaurant, and the codes appear each time a violation takes place within said inspection. I need to add up the point values of code violations in each row/inspection. Additionally, do I need to remove the descriptions with the codes? Just now thought about the fact that I might need to
