COUNTIFS Calculating Incorrectly

[wdgor]

This is driving me crazy, i.e. why doesn't it work?

The expression below evaluates to 0 even though there are values in D5 and D9. I expected the result of this expression to be 2.

="All Project Count - "&COUNTIFS('New Deal Worksheet'!D5,">0",'New Deal Worksheet'!D9,">0",'New Deal Worksheet'!D13,">0",'New Deal Worksheet'!D17,">0",'New Deal Worksheet'!D21,">0",'New Deal Worksheet'!D25,">0",'New Deal Worksheet'!D29,">0",'New Deal Worksheet'!D33,">0",'New Deal Worksheet'!D37,">0",'New Deal Worksheet'!D41,">0",'New Deal Worksheet'!D45,">0",'New Deal Worksheet'!D49,">0",'New Deal Worksheet'!D53,">0",'New Deal Worksheet'!D57,">0",'New Deal Worksheet'!D61,">0",'New Deal Worksheet'!D65,">0",'New Deal Worksheet'!D69,">0",'New Deal Worksheet'!D73,">0",'New Deal Worksheet'!D77,">0",'New Deal Worksheet'!D81,">0")

If I use =COUNTIFS('New Deal Worksheet'!D5,">0") it correctly evaluates to 1.
But if I use =COUNTIFS('New Deal Worksheet'!D5,">0",'New Deal Worksheet'!D9,">0"), it also evaluates to 1 when it should be 2. ?????????????? Thanks for any insight.

 

[Fluff]

That will only ever return 1 or 0 as the arguments in a countifs are treated as AND rather than OR.
How about

=SUMPRODUCT(('New Deal Worksheet'!D5:D81>0)*(MOD('New Deal Worksheet'!D5:D81,4)=1))

 

[wdgor]

I didn't know that COUNTIFS evaluated as an AND condition. I always thought that it would evaluate each expression on its own, i.e. if the first expression is > 0 it would evaluate it alone then the next expression. I learned something today - thank you!

The sumproduct formula you suggested evaluated to 1 and not 2. Also, each one of the elements in the expression I used is based on a specific employee type in the sheet, i.e. D5 is Implementations, D6 is QA, D7 is TPM and D8 is PM (see below). So, I cannot evaluate the entire range. I was trying for a formula that would evaluate each type and gave only one type in the original example. See table below. This repeats for every project and there can be 50 or more projects. I essentially want a count of how many projects are active. In order for a project to be active, it needs a start date.

Role	Start Date
Implementation Engineer	3/2/2022 This is D5
QA Team	4/2/2022
TPM	3/6/2022
PM	3/2/2022
Implementation Engineer	5/3/2022 This is D9
QA Team	6/2/2022
TPM	3/3/2022
PM	4/3/2022
Implementation Engineer	This is D13
QA Team
TPM
PM

 

[Fluff]

Oops, it should have been

=SUMPRODUCT((D5:D81>0)*(MOD(ROW(D5:D81),4)=1))

or another way

=SUMPRODUCT((D5:D81>0)*(C5:C81="Implementation Engineer"))

 

[wdgor]

PERFECT!! That solved the problem. Thanks a lot. I learn something new each day - its great to have Excel wizards who are willing to help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[wdgor]

Just for grins, I also tried this (this was just a play range):

=COUNTIFS((H3,H4,H7,H9,H12,H14,H17),">0")

Funny how assumptions you always had in your head don't pan out in reality. Thanks again for your help.