1. SUMPRODUCT/DATEDIF (2003)

I am trying to figure out a formula array that will produce a count value looking at a value criteria and a date criteria. I want to accommodate a greater than and less date criteria. My end state is counting certain types of records that fall in specified date windows.

The date criteria is custom formatted d-mmm-yy.

=(SUMPRODUCT((\$B\$3:\$B\$23="Red")*(DATEDIF(\$C\$3:\$C23 >"30-Sep-07"))*(DATEDIF(\$C\$3:\$C23<1-Jan-08)))

This obviously doesn't work, but it is almost there

2. Re: SUMPRODUCT/DATEDIF (2003)

=SUMPRODUCT((\$B\$3:\$B\$23="Red")*(\$C\$3:\$C23>DATE(200 7,9,30))*(\$C\$3:\$C23<DATE(2008,1,1)))

Steve

3. Re: SUMPRODUCT/DATEDIF (2003)

Steve,
Thanks. That worked well. I have added another count criteria to the formula array. I want to count by Organization (reference attached workbook), but use a wildcard or something like that.

The primary organizations are "A-1" and "A-2", but each record has the organization defined more specifically--that is, some records have a sub-organization of "A-1" or "A-2", like "A-11" or "A-223".

I would want the formula below to include *(\$D\$3:\$D\$23="A-1*) or something like that assuming "*" can be used as a wildcard. Excel doesn't seem to want to accept that thought.

Amy

4. Re: SUMPRODUCT/DATEDIF (2003)

=SUMPRODUCT((\$B\$3:\$B\$23="Red")*(\$C\$3:\$C23>DATE(200 7,9,30))*(\$C\$3:\$C23<DATE(2008,1,1))*(LEFT(\$D\$3:\$D\$ 23,3)="A-1"))

You can not use a wildcard by Left(something,3)="A-1" is equivalent to "A-1*"

Steve

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•