Forum Discussion
Better Alternative to IF COUNT or Use of IF COUNT IN A RANGE
Hi Guys, I am currently using an IF Count Function on to sort out the statement below. So as you can see there is different types of transaction.I am using IF COUNTIF here and drag it for eac...
Dimi23710 You can use SUM(COUNTIF(...)) with a criteria array to include multiple criteria values in a single COUNTIF formula. For example, the formula shown in your second screenshot can be simplified as follows:
=IF(SUM(COUNTIF(B11, "*" & {"virgin","starlink"} & "*")), C11, 0)
The static array shown above can also be replaced with a range of values (but make sure the criteria range does not include any blank cells). For example:
=IF(SUM(COUNTIF(B11, "*" & Sheet2!$C$3:$C$4 & "*")), C11, 0)
Also, as an alternative to COUNTIF, you could use ISNUMBER/SEARCH as follows:
=IF(SUM(--ISNUMBER(SEARCH(Sheet2!$C$3:$C$4, B11))), C11, 0)
NOTE: with older versions of Excel, use SUMPRODUCT instead of SUM (or press Ctrl+Shift+Enter when inputting any of the formulas shown above).
