Using INDEX with SUMIFS?

[jaxso915]

In one spreadsheet, titled "Code Sheet", in column "A" I have the names of health codes. In column "B" I have point values that correspond with the codes in column "A" (1-5points). In a second spreadsheet, titled "2022", the names of the health codes from "Code Sheet" appear throughout each row of this sheet periodically. Is there any way, using the INDEX and SUMIF functions, or any other functions, that I can add up the corresponding point values of the code names that appear throughout each row of the "2022" spreadsheet? Every time a health code from "Code Sheet" appears in a row of "2022", I need to add the point values up. I need a total # of points for each row of my "2022" sheet. Is there any way I can do this without changing the entire format of my sheets? If you need clarification, let me know!

[#2]

@jaxso915 

=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.

[#3]

@jaxso915 

For me the start was to use XLOOKUP to convert the codes that may appear anywhere within a range into the equivalent values.  From there the values may be aggregated any way you choose.  For example

= LET(
    value2020, XLOOKUP(data2020, Codes, Values, 0),
    BYROW(value2020, LAMBDA(x, SUM(x)))
  )

Using insider beta that reduces to

= LET(
    value2020, XLOOKUP(data2020, Codes, Values, 0),
    BYROW(value2020, SUM)
  )

[#4]

@Peter Bartholomew are Value2020 and data2020 the datasheets in your formula? If not that are they functioning as?

[#5]

@jaxso915 

I defined the name 'data2020' to be the entire range that contains data on sheet 2020.  The formula examines every cell of the range to determine whether it can be found in the list and, if so, returns a value 'value2020' that in the numerical equivalent of the health code.  From there I have summed the rows.

[#6]

@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?

[#7]

This is what I have put as my equation:
=LET(value2022, XLOOKUP(data2022, 'Code Sheet'!$A$2:$A$144, 'Code Sheet'!$B$2:$B$144, 0), BYROW(value2022, SUM))
It comes back as a name error. What do I do from here?

[#8]

@jaxso915 

=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.

[#9]

@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

[#10]

@jaxso915 

=SUM(IF(A2:I2='Code Sheet'!$A$2:$A$9,'Code Sheet'!$B$2:$B$9))

Yes i'd remove the descriptions in the "2022" sheet because it simplifies the calculation.

[#11]

@jaxso915 

The key to my formulas is the defined names used to identify data ranges (highlighted in yellow in the workbook).  That and the fact that I always use 365 functionality.

[#12]

@OliverScheurich Apologies for the late response to your reply. I have used your formula, but all I get as an output is "0". I have attached a snip of the issue. Any ideas? Thanks again for your help with this.

[#13]

@jaxso915 

Which version of Excel do you use? If you don't work with Office 365 or Excel for the web or Excel 2021 you have to enter the formula as an arrayformula with ctrl+shift+enter.

Here is an explanation about arrayformulas and ctrl+shift+enter.

Create an array formula - Microsoft Support

[#14]

@OliverScheurich I believe that my company uses Microsoft Office Professional Plus 2019