Can COUNTIFS formulas be combined with OR formulas?

[Deman]

Hi
I have a simple COUNTIFS formula

[/SIZE]
[FONT=Arial]=COUNTIFS(Duties,DutieA,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]
[FONT=Arial][SIZE=1]

[/FONT]<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have 7 different duties for 1 group so to include these I have the formula
<o></o>

[/SIZE]
[/FONT][FONT=Arial]=COUNTIFS(Duties,DutieA,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]
[FONT=Arial]+ COUNTIFS(Duties,DutieB,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]
[FONT=Arial]+ COUNTIFS(Duties,DutieC,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]
[FONT=Arial]+ COUNTIFS(Duties,DutieD,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]
[FONT=Arial]And so on[/FONT]
[FONT=Arial][SIZE=1]

<o></o>
I have been playing about trying to put a OR formula inside with no joy, is there any way to simplify this because I need to copy this formula about 120 times and teak it slightly for each group and would be easier for me to do.
Some types of work only have one Dutie but some have up to ten,
Duties, Company, Discipline are ranges (entire columns) on a different sheet where data is inputted, CompanyA DutiesA DuitesB refer to single cell location on a hidden sheet.
Thanks in advance for any response and my apolises if this has been posted before, its a nightmare searching for the word OR

 

[Yahya]

i don't know if i understand you correctly
try this formula

=SUMPRODUCT(COUNTIFS(Duties,{"DutieA";"DutieB";"DutieC";"DutieD"},Company,CompanyA,Date,$B$5,Discipline,DisciplineA))

 

[Aladin Akyurek]

Are DutieA, CompanyA, DisciplineA placeholders (variables) like Date, Duties, Company, and Discipline?

 

[T. Valko]

Hi
I have a simple COUNTIFS formula

[FONT=Arial]=COUNTIFS(Duties,DutieA,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have 7 different duties for 1 group so to include these I have the formula
<o></o>

[FONT=Arial]=COUNTIFS(Duties,DutieA,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]
[FONT=Arial]+ COUNTIFS(Duties,DutieB,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]
[FONT=Arial]+ COUNTIFS(Duties,DutieC,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]
[FONT=Arial]+ COUNTIFS(Duties,DutieD,Company,CompanyA,Date,$B$5,Discipline,DisciplineA)[/FONT]
[FONT=Arial]And so on[/FONT]

<o></o>
I have been playing about trying to put a OR formula inside with no joy, is there any way to simplify this because I need to copy this formula about 120 times and teak it slightly for each group and would be easier for me to do.
Some types of work only have one Dutie but some have up to ten,
Duties, Company, Discipline are ranges (entire columns) on a different sheet where data is inputted, CompanyA DutiesA DuitesB refer to single cell location on a hidden sheet.
Thanks in advance for any response and my apolises if this has been posted before, its a nightmare searching for the word OR

Let's assume DutieA thru DutieD are the cells A1:A4.

Then:

=SUMPRODUCT(--(ISNUMBER(MATCH(Duties,A1:A4,0))),--(Company=CompanyA),--(Date=$B$5),--(Discipline=DisciplineA))

 

[Deman]

Thank you T.Valko

The Duites were listed in seperated cells on a different sheet which i hide, i did it this way so it will be easier for people that arent up to speed in Excel can easily edit Duties.

Thank you all for your responses and my apoligies for not being totally clear with my request.

 

[T. Valko]

Thank you T.Valko

The Duites were listed in seperated cells on a different sheet which i hide, i did it this way so it will be easier for people that arent up to speed in Excel can easily edit Duties.

Thank you all for your responses and my apoligies for not being totally clear with my request.

Ok, is your question resolved?

 

[Deman]

yes thank you the above question is answered and your solution is working great, however I am now doing another formula for to SUM the amount of hours rather than COUNT the manpower.

=SUMIFS(Hours,Protection,$M$2,Duties,Labels!B6:J6,Date,$B$5)

Again the problem is with the multiple amount of duties, how do i get this to work, the duties are listed on a sheet called labels in cells B6:J6. I dont understand how the formula you provided works so i don't know how to tweak it from counting to suming.

Thanks in advanced for any more information, you have saved me so much time already.

 

[Deman]

Sorted

=SUMPRODUCT(--(ISNUMBER(MATCH(Duties,Labels!$B6:$J6,0))),--(Protection=M$2),--(Date=$B$5),Hours)

That sumproduct is an awesome function, thanks very much for your help

 

[T. Valko]

Sorted

=SUMPRODUCT(--(ISNUMBER(MATCH(Duties,Labels!$B6:$J6,0))),--(Protection=M$2),--(Date=$B$5),Hours)

That sumproduct is an awesome function, thanks very much for your help

Good deal. Thanks for the feedback!