CODEWITHSUNDEEP
excelexcel-formula
bluefox
bluefox

Reputation: 175

Excel formula to get number of rows that fulfill 2 criteria

I have an excel list with around 5000 entries and 15 columns.

The goal is to display in one cell the result of the following query:

=(Number of rows where ColumnB Like "*123*" and ColumnC like "*House*")

It seems that normal VLOOKUP will not help because it only displays the first result. On the other hand formulas with COUNTIF etc. do not work because either it is not possible to use wildcards (*) or it is not possible to enter more than one search criteria.

Unfortunately it is a shared excel list, so I cannot use VBA.

Do you have any suggestions how I can implement it by the use of formulas?

Upvotes: 0

Views: 168

Answers (1)

BigBen
BigBen

Reputation: 49998

  1. If column B contains text: 

    =COUNTIFS(B:B,"*123*",C:C,"*House*")

  2. If column B contains numbers (which wildcards will not work with):

    =SUMPRODUCT(ISNUMBER(SEARCH(123,B2:B4))*ISNUMBER(SEARCH("House",C2:C4)))

    changing the range references as needed.

Upvotes: 2

Related Questions