• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help on Countifs formula with multiple conditions

A

ajay_1151

New Member
Hi Chandoo, need your help. I am trying to apply Countifs formula with multiple conditions, however getting #value! error. Please help me with this. I have attached an excel workbook, on the 'Summary' worksheet I am trying to apply the formula.
 

Attachments

Hi Ajay,

You are getting error since in the third criteria you are passing the two-dimensional range, and COUTIFS seems to be work on single dimension. Use below formula in D5 and copy down.

=SUMPRODUCT(--(Summary!$B5=Table1[Process]),--(Table1[Employee Status]=Summary!$C5),MMULT(--(Table1[[Project 1]:[Project 10]]="Yes"),{1;1;1;1;1;1;1;1;1;1}))

Change Yes to No and NA as per column.

See the file.

Regards,
 

Attachments

Last edited:
I am actually trying to count "Yes" values for Team A and Status Active. You can check the formula which I have applied on Cell D5, you will get an idea what I am looking for.
 
Hi Ajay ,

Very reluctant to give a quantitative answer to a simple question !

I asked the question because the formula gives an error value ; do you expect another formula which will also give an error value ?

Anyway , if you can rearrange your headers slightly , you can get one set of values ; see the attached file. I do not know whether these are what you expect to see.

Narayan
 

Attachments

Somendra Misra said:
Hi Ajay,

You are getting error since in the third criteria you are passing the two-dimensional range, and COUTIFS seems to be work on single dimension. Use below formula in D5 and copy down.

=SUMPRODUCT(--(Summary!$B5=Table1[Process]),--(Table1[Employee Status]=Summary!$C5),MMULT(--(Table1[[Project 1]:[Project 10]]="Yes"),{1;1;1;1;1;1;1;1;1;1}))

Change Yes to No and NA as per column.

See the file.

Regards,
Click to expand...
 
Somendra Misra said:
Hi Ajay,

You are getting error since in the third criteria you are passing the two-dimensional range, and COUTIFS seems to be work on single dimension. Use below formula in D5 and copy down.

=SUMPRODUCT(--(Summary!$B5=Table1[Process]),--(Table1[Employee Status]=Summary!$C5),MMULT(--(Table1[[Project 1]:[Project 10]]="Yes"),{1;1;1;1;1;1;1;1;1;1}))

Change Yes to No and NA as per column.

See the file.

Regards,
Click to expand...


Hi Somendra, perfect answer :) this is what I was looking for. Thanks a ton :)
 
NARAYANK991 said:
Hi Ajay ,

Very reluctant to give a quantitative answer to a simple question !

I asked the question because the formula gives an error value ; do you expect another formula which will also give an error value ?

Anyway , if you can rearrange your headers slightly , you can get one set of values ; see the attached file. I do not know whether these are what you expect to see.

Narayan
Click to expand...

No problem Narayan, Somendra has provided me the appropriate answer. thanks for your help.
 
@ajay_need_help

This is not a personal comment, but you must follow some etiquette, Narayan Sir had also provided a right solution. You must respect those who try to help you. People are not paid here to help you. They help out of their interest. If you keep a good attitude you will be helped again.



Regards,
 
Somendra Misra said:
@ajay_need_help

This is not a personal comment, but you must follow some etiquette, Narayan Sir had also provided a right solution. You must respect those who try to help you. People are not paid here to help you. They help out of their interest. If you keep a good attitude you will be helped again.



Regards,
Click to expand...

Please accept my apologies Somendra and Narayan; did not mean to hurt anyone's sentiments here, I would like to say thanks to both of you for showing interest in resolving my issue.
 
You must log in or register to reply here.
Back
Top