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