I have a simple and small file which does some counting in an external workbook. The data in this external workbook is filtered using autofilter (for example: a specific time range). Let's name the small file 'output' and the external workbook 'input'.
I have some standard values on my file 'output' to search for in the specific columns in the file 'input'. I need to search for records that match multiple conditions and count the matching records for example.
I found an excel formula which does almost perfectly what I want, except this searches for the value from the left. Any typo's from the person who enters the data will make the formula skip that record. I need to adjust the formula to search for the string in the cell value, indepent of the position of that specific string.
For example: It needs to search for the string 'kat'. But when in the file 'input' is entered ' kat' (so with a leading space), the record should be taken into account.
The working formula I found is this:
=SOMPRODUCT(SUBTOTAAL(3;VERSCHUIVING('[online test file input.xlsx]Blad1'!$E:$E;RIJ('[online test file input.xlsx]Blad1'!$E:$E)-MIN(RIJ('[online test file input.xlsx]Blad1'!$E:$E));;1));--(LINKS('[online test file input.xlsx]Blad1'!$E:$E;3)=LINKS($A6;3))*(('[online test file input.xlsx]Blad1'!$C:$C)=B$5))+SOMPRODUCT(SUBTOTAAL(3;VERSCHUIVING('[online test file input.xlsx]Blad1'!$E:$E;RIJ('[online test file input.xlsx]Blad1'!$E:$E)-MIN(RIJ('[online test file input.xlsx]Blad1'!$E:$E));;1));--(LINKS('[online test file input.xlsx]Blad1'!$E:$E;6)=RECHTS($A6;6))*(('[online test file input.xlsx]Blad1'!$C:$C)=B$5))
Because I am using the dutch version of Excel, here is the translation of the used functions: somproduct = sumproduct , subtotaal = subtotal , verschuiving = offset , rij = row , links = left
because there are 2 values the output file searches for now, the formula is actually 2 formulas which are the same, but the condition they are searching for is different (kat versus katige).
I asked ChatGPT to write the formula for me so it checks for records that matches 2 conditions and where the first condition is to match the string 'kat' (which is the result of Links(A6;3), whether or not it is with or without capitals) with the values in column E in file 'input'. this is the formula in cell C6:
=SOMPRODUCT( SUBTOTAAL(3; VERSCHUIVING('[online test file input.xlsx]Blad1'!$E:$E; RIJ('[online test file input.xlsx]Blad1'!$E:$E) - MIN(RIJ('[online test file input.xlsx]Blad1'!$E:$E));;1)); --(ZOEKEN(LINKS($A6; 4); ('[online test file input.xlsx]Blad1'!$E:$E)) > 0) * (('[online test file input.xlsx]Blad1'!$C:$C) = C$5) )
The formula works. But returns an incorrect value: 4 where it should be 10. When going into the file 'input' 10 is the number of total records shown for NIJ, but only 4 records match both conditions with string 'kat' somewhere in the value in column E.
I have tried to change the formula and to find why the formula returns 10 instead of 4.
I created a similar table with fake data:
this is the input table:
this formula =SOMPRODUCT(SUBTOTAAL(3;VERSCHUIVING('[online test file input.xlsx]Blad1'!$E:$E;RIJ('[online test file input.xlsx]Blad1'!$E:$E)-MIN(RIJ('[online test file input.xlsx]Blad1'!$E:$E));;1));--(('[online test file input.xlsx]Blad1'!$C:$C)=C$5))
returns 10 for NIJ which is correct. But when I want to add a second condition which checks for a part of the value in column E to be equal to the left 3 characters in cell A6, the formula does not work anymore.
two seperate formulas which both use the left function, will work. But that method will not work when at entry level, the user enters ' kat' instead of 'kat'. I would like to understand why that happens.
this is the output table:
This formula turned out to be the solution for my situation: =SOMPRODUCT(SUBTOTAAL(3;VERSCHUIVING('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C;RIJ('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C)-MIN(RIJ('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C));;1))*('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C=LINKS(B$4;3))*ISGETAL(VIND.SPEC(LINKS(E7;4);'[weekupdate.xlsx]Vluchten richting Nederland'!$G:$G)))
(I moved the search value to E7)
This formula turned out to be the solution for my situation:
=SOMPRODUCT(SUBTOTAAL(3;VERSCHUIVING('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C;RIJ('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C)-MIN(RIJ('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C));;1))*('[weekupdate.xlsx]Vluchten richting Nederland'!$C:$C=LINKS(B$4;3))*ISGETAL(VIND.SPEC(LINKS(E7;4);'[weekupdate.xlsx]Vluchten richting Nederland'!$G:$G)))(I moved the search value to E7)