Forum Discussion
if a date is before an appointment, then count certain words in a different range
I need to count how many entries with certain words are in the range B1:G10 if the date in column A is before the entry in I1. My formula in I2
=IF(@A:A<I1;COUNTIF(B2:G10,"*Natz*")+COUNTIF(B2:G10,"*Verd*")+COUNTIF(B2:G10,"*City*")+COUNTIF (B2:G10; "*Hint*"); "nothing")
is wrong, it outputs all values. The result in I3 is also wrong when I want to search with
=IF(@A:A>I1;COUNTIF(B2:G10,"*Natz*")+COUNTIF(B2:G10,"*Verd*")+COUNTIF(B2:G10,"*City*")+COUNTIF (B2:G10; "*Hint*"); "nothing")
all entries after the appointment in I1. Where is my mistake? For answers, I would be very thankful!
=SUMPRODUCT((A2:A10<>"")*(A2:A10<I1)*(ISNUMBER(SEARCH("Hinz", B2:G10))+ISNUMBER(SEARCH("Natz", B2:G10))+ISNUMBER(SEARCH("Stadt", B2:G10))+ISNUMBER(SEARCH("Verd", B2:G10))))
=SUMMENPRODUKT((A2:A10<>"")*(A2:A10<I1)*(ISTZAHL(SUCHEN("Hinz"; B2:G10))+ISTZAHL(SUCHEN("Natz"; B2:G10))+ISTZAHL(SUCHEN("Stadt"; B2:G10))+ISTZAHL(SUCHEN("Verd"; B2:G10))))
