Log In
Register
Home
Active Member
January 13, 2020
Offline
Please correct my formula
=COUNTIFS(DIS!B:B,{"Taimani","chaman","Khushalkhan"},DIS!E:E,{"Group Loan","GMRB"},DIS!F:F,{"Trade","Trade SS"})
See Answer
November 8, 2013
Offline
Try:
=COUNTIFS(DIS!B:B,"Taimani",DIS!B:B,"chaman",DIS!B:B,"Khushalkhan",DIS!E:E,"Group Loan",DIS!E:E,"GMRB")
Trusted Members
Moderators
November 1, 2018
Offline
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.
Answers Post
Active Member
January 13, 2020
Offline
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
Active Member
January 13, 2020
Offline
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"))
Trusted Members
Moderators
November 1, 2018
Offline
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)
1 Guest(s)
