Nested formula, repeat countif function using vlookup for an array of data
I am trying to create an excel formula in a database that will first find a loan officer code in one column (E), then on the row where a particular officer code was found, will look in column K for letter "J", if letter "J" is found the formula should count it and then continue to count up the J""'s for that particular loan officer code until it has searched the entire spreadsheet. Is that possible?
»
- Add new comment
- 422 reads
Did you try the Vlookup
Did you try the Vlookup function?
You can make a column with this function.
=IF(E3=loan_officer;IF(NOT(ISERR(FIND(search_text;K3;1)));1;0);0)
In this example loan_officer is a named range in which you put the number of the searched officer.
search_text is a named range in which you put a "J" or the text you want to find.
you could replace search_text by "J" but that would limit the choice of text.
Only thing you have to do next is sum the column.
I got something to work
I would not claim to be an excel expert, so I didn't know how to use the function that you posted. I do thank you for the response though. I ended up rearranging the data so I could use vlookup to find all of the officer codes that had a "J" in the same row. So I created a new column for that function. Then I used countif to count the instances of a particular officer code in the new column.