CODEWITHSUNDEEP
excelexcel-formulacountif
Marcin Konopka
Marcin Konopka

Reputation: 57

Countifs formula with criteria value in array

I have a list of accounts and am creating a countifs formula with multiple variables, based on a changelog in a separate sheet.

Currently it stands like this (please note the values in quotation marks are distinct TEXT values as they are system codes and not numeric value):

=COUNTIFS(Changelog!$A$1:$A$1000,A1,Changelog!$L$1:$L$1000,"=3",Changelog!G$1:G$1000,{"=993","=Z94","=Z95","=Z96","=Z97","=998","=999"})

As you can notice there are 3 types of variables that create the criteria. First 2 are rather self explanatory: 1st we have a search in changelog for the account in the A1 cell (so we will have a list of changes on each account), 2nd is a search for those whose respective L cell value equals to 3.

So far so good. However the problem arises for me with including the third condition. I want to check whether the account has one of the values in a G column. The formula works only for the first value (that being "993").

For example, If in the changelog there is a line in which in column A value is equal to the account number, column L will have the value of "3" and in column G there is a value of "993", than the count will show "1". If however the G column has a value of (for example) "Z95", than the formula shows 0.

I wonder how it can be done to implement such a "logical OR" criteria that would take any of the values into account.

Upvotes: 2

Views: 145

Answers (2)

JvdV
JvdV

Reputation: 75840

Here is an example on how you can approach this:

=SUMPRODUCT((Changelog!A1:A1000=A1)*(Changelog!G1:G1000={"993","Z94","Z95","Z96","Z97","998","999"})*(Changelog!L1:L1000="3"))

Upvotes: 2

Mikku
Mikku

Reputation: 6654

Tested

=SUM(COUNTIFS(Changelog!$A$1:$A$1000,A1,Changelog!$L$1:$L$1000,"=3",Changelog!G$1:G$1000,{"993","Z94","Z95","Z96","Z97","998","999"}))

Add Sum in Front

Remove = Sign

Upvotes: 2

Related Questions