Better Alternative to IF COUNT or Use of IF COUNT IN A RANGE

Copper Contributor

Hi Guys,

I am currently using an IF Count Function on to sort out the statement below.

Dimi23710_0-1719525544876.png

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.

Dimi23710_1-1719525984264.png

 

 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.

Dimi23710_2-1719526212303.png

 

 

Anyone has an idea on how to achieve that?

 

 

Thanks for taking the time to read this.

 

 

 

1 Reply

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