Search code examples
excel-formulasubstringvlookup

How return substring of count for override for found december?

I have a spreadsheet that will always return varying row contents. So I need to search each row to find the january, february, march, april, may, june, july, etc content and extract the counts from that in the cell with substring. I'm not certain how to do this since the location in the row will always vary. Any ideas? I was thinking vlookup or index or match, or mid/search, but I'm not sure.

Example spreadsheet content, notice some rows have the overallCount and some have overrideCount, and a string like "overallCount 2022/12 35" is the complete cell value, where 2022 is the year, and 12 is the month:

result1                     result2                     result3                  result4        
overallCount 2023/01 11     overallCount 2022/12 35     overallCount  2022/01 12 overrideCount 2022/08 5
overallCount 2023/02 1      overallCount 2022/11 34     overrideCount 2022/02 9  overrideCount 2022/01 5
------                      (3 rows)                    OverrideYearOrders       overrideCount 2022/10 2               overallCount 2023/01 6

And the rows can have varying column numbers as well if there are more or less orders for each device.

I want to add a formula in result8 column or so, and search to the left of each row, and pull out the counts in columns for Jan, Feb, March, April, May, etc. I was thinking of something like this:

=VLOOKUP("overrideCount 2022/12",H6:AC8517,1,FALSE)

but it's not returning the count. It's returning #N/A

example per-row output on the right:

Overall                                                                       Override
Jan2022  Feb  March  April May  June  July  Aug  Sept Oct  Nov  Dec Jan2023   Jan2022 Feb  Mar ...
                                                                35  11        5

As you can see, not every month has a count that would be found in the row. How would I pull those monthly counts for overall and override out to the right in each row?

Update*

This is the generated table per comment below:

result1 result2 result3 result4 result5 Output to right here Overall Jan2022 Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Override Jan2023 Override Jan2022 Feb
overallCount 2023/01 11 overallCount 2022/12 35 overallCount 2022/01 12 overrideCount 2022/01 5 35 11 5
overallCount 2023/02 1 overallCount 2022/11 34 overrideCount 2022/02 9 overrideCount 2022/01 5 34 5 9
------ (3 rows) OverrideYearOrders overrideCount 2022/10 2 overallCount 2023/01 6 6

Example output is shown to right in generated table above, and also example per-row output is given in this before this added update already.

Solution

  • FORMULA:

    This formula will get you to where you want pending a couple changes.

    1. Rename the column headers to represent the date you want to calculate for (1/1/2022, 2/1/2022, ...).
    2. This formula requires every cell value to "search" to be of "overallCount yyyy/mm" or "overrideCount yyyy/mm" format

    Drag and drop the formula for as far as you need it (row and columns).

    First formula, totaling overallCount (F2:s2)

    =IF(SUMPRODUCT(--IFERROR(SEARCH("overallCount "&TEXT(F$1,"yyyy/mm"),$A2:$E2)>0,FALSE)*SUMPRODUCT(--IFERROR(RIGHT($A2:$E2,LEN($A2:$E2)-(FIND("overallCount "&TEXT(F$1,"yyyy/mm"),$A2:$E2)+20)),0)))>0,SUMPRODUCT(--IFERROR(SEARCH("overallCount "&TEXT(F$1,"yyyy/mm"),$A2:$E2)>0,FALSE)*SUMPRODUCT(--IFERROR(RIGHT($A2:$E2,LEN($A2:$E2)-(FIND("overallCount "&TEXT(F$1,"yyyy/mm"),$A2:$E2)+20)),0))),"")

    Second formula, totalling overrideCount (R2:AG2)

    =IF(SUMPRODUCT(--IFERROR(SEARCH("overrideCount "&TEXT(T$1,"yyyy/mm"),$A2:$E2)>0,FALSE)*SUMPRODUCT(--IFERROR(RIGHT($A2:$E2,LEN($A2:$E2)-(FIND("overrideCount "&TEXT(T$1,"yyyy/mm"),$A2:$E2)+21)),0)))>0,SUMPRODUCT(--IFERROR(SEARCH("overrideCount "&TEXT(T$1,"yyyy/mm"),$A2:$E2)>0,FALSE)*SUMPRODUCT(--IFERROR(RIGHT($A2:$E2,LEN($A2:$E2)-(FIND("overrideCount "&TEXT(T$1,"yyyy/mm"),$A2:$E2)+21)),0))),"")

    EXPLANATION:

    • The formula is using the SUMPRODUCT function which multiplies the corresponding elements of the arrays and returns the sum of products.
    • The formula uses the SEARCH function to look for the occurrence of the string in the cells using the format "yyyy/mm", in the cells of the range A2:E2 and returns TRUE if found, otherwise returns FALSE.
    • The formula uses the IFERROR function to check for errors and returns 0 if error is found.
    • The formula uses the RIGHT function and is used to extract the rightmost part of the cell, starting from the position of the found string -20/-21 (calculation that takes advantage of the date in the column header plus its position in order to autofill). In short, it totals the number after the date.

    EXAMPLE:

    Example

    The following formula is the root formula that is just wrapped in an IF THEN statement to show empty cells instead of 0's. This formula is written assuming Cells A1:E1 are the 'results' headers, and starting at Cell F1 is the date start of your dates. (Jan22-Feb23 of each category)

    SUMPRODUCT(--IFERROR(SEARCH("overallCount "&TEXT(F$1,"yyyy/mm"),$A2:$E2)>0,FALSE)*SUMPRODUCT(--IFERROR(RIGHT($A2:$E2,LEN($A2:$E2)-(FIND("overallCount "&TEXT(F$1,"yyyy/mm"),$A2:$E2)+20)),0)))