Forum Discussion

Dimi23710's avatar
Dimi23710
Copper Contributor
Jun 27, 2024

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 each line to sort out the amount i want in Phone (Virgin Media here is a phone provider and the formula is to categorize them as such) "Virgin" being the keyword for my selection.

 

Now let's say i have different suppliers. In the case below, i am adding Starlink Telecom and  the formula will work as follow.

 

 The problem i have with this is the fact that i manually have to add every phone provider in my formula.

 

What i am trying to do instead is link the two phones suppliers here to list i would have made in another worksheet. The formula will then just read into the column C, pick the names and add the amount in the original sheet once there is a translation that contain those keyworlds.

 

 

Anyone has an idea on how to achieve that?

 

 

Thanks for taking the time to read this.

 

 

 

  • djclements's avatar
    djclements
    Bronze Contributor

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

Resources