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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
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)
