if a date is before an appointment, then count certain words in a different range

Copper Contributor

Excel-Datumsabfrage.gif

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!

2 Replies

@Carmen60

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

@HansVogelaar 

 

Dear Mr. Vogelaar,

this was really fast and it works - thousand thanks! :happyface::happyface: