Forum

Notifications
Clear all

Countifs formula

6 Posts
3 Users
0 Reactions
77 Views
(@akbarafshar)
Posts: 3
Active Member
Topic starter
 

Please correct my formula

=COUNTIFS(DIS!B:B,{"Taimani","chaman","Khushalkhan"},DIS!E:E,{"Group Loan","GMRB"},DIS!F:F,{"Trade","Trade SS"})

 
Posted : 14/01/2020 5:54 am
(@catalinb)
Posts: 1937
Member Admin
 

Try:
=COUNTIFS(DIS!B:B,"Taimani",DIS!B:B,"chaman",DIS!B:B,"Khushalkhan",DIS!E:E,"Group Loan",DIS!E:E,"GMRB")

 
Posted : 15/01/2020 6:09 am
(@debaser)
Posts: 836
Member Moderator
 

You can't use three criteria arrays (assuming you want all possible combinations) like that. Are there other options in column F that begin with "Trade" that you need to exclude? If not, you could use:

 

=SUM(COUNTIFS(DIS!B:B,{"Taimani","chaman","Khushalkhan"},DIS!E:E,{"Group Loan";"GMRB"},DIS!F:F,"Trade*"))

 

Note the semicolons in the second array rather than commas.

 
Posted : 15/01/2020 12:55 pm
(@akbarafshar)
Posts: 3
Active Member
Topic starter
 

Catalin Bombea said
Try:
=COUNTIFS(DIS!B:B,"Taimani",DIS!B:B,"chaman",DIS!B:B,"Khushalkhan",DIS!E:E,"Group Loan",DIS!E:E,"GMRB")  

Thank you Catalin Bombea but it does not work

 
Posted : 16/01/2020 7:12 am
(@akbarafshar)
Posts: 3
Active Member
Topic starter
 

Thank you so much Mr Velouria  it is so helpful and if I want to extend my formula such below so could you please fix it to me?

=SUM(COUNTIFS(DIS!B:B,{"Taimani","chaman","Khushalkhan"},DIS!E:E,{"Group Loan";"GMRB"},DIS!F:F,{"Trade","Services","Agriculture"},DIS!C:C,"<200000"))

 
Posted : 16/01/2020 7:16 am
(@debaser)
Posts: 836
Member Moderator
 

I don't give out contact info, I'm afraid.

 

As I mentioned before, you cannot use more than two arrays in a formula like this. I would suggest you use DCOUNT instead. For that you will need a spare couple of cells somewhere for the criteria formula - let's say cells F1 and F2. Leave the first cell (F1) blank, then in the one underneath (F2) enter this formula:

 

=AND(OR(DIS!B2={"Taimani","chaman","Khushalkhan"}),OR(DIS!E2={"Group Loan","GMRB"}),OR(DIS!F2={"Trade","Services","Agriculture"}),DIS!C2<200000)

 

and then your count formula becomes simply:

=DCOUNTA(DIS!B:F,1,F1:F2)

 
Posted : 17/01/2020 6:50 am
Share: