Array Formula 4 Criteria, One of them Multiple Option

I am pulling a report together from a data worksheet.

I need to pull with the following 4 criteria.
- program element
- after a certain start date
- before a certain end date
- a contract number within a range specified


If I pull from only one cell representing the contract number I have no problems. But when I try to pull from a range, I get an error.

Here is my formula
=SUMPRODUCT(--('PPV Data'!$I$8:$I$21111>=$AD$7),--('PPV Data'!$I$8:$I$21111<=$AD$8),--('PPV Data'!$H$8:$H$21111="JP"),--('PPV Data'!$Q$8:$Q$21111=AA8:AA41))

PPV Data sheet cells
"I" are dates
"H" is the program element
"Q" is the contract number

so in my report worksheet I have cells to enter the contract numbers I want included, this could range from 1 contract up to 33 contracts. I want the report to tell me, from the contract numbers entered in cells AA8 through AA41, There are X number of cells with the JP program element between the two dates entered.

If I use only a single contract number and reference only a single cell in this part of the formula ---('PPV Data'!$Q$8:$Q$21111=AA8:AA41) I have no problem.

Ideas?

Can you upload example workbook?

Originally Posted by excelmabel2013

I am pulling a report together from a data worksheet.

I need to pull with the following 4 criteria.
- program element
- after a certain start date
- before a certain end date
- a contract number within a range specified


If I pull from only one cell representing the contract number I have no problems. But when I try to pull from a range, I get an error.

Here is my formula
=SUMPRODUCT(--('PPV Data'!$I$8:$I$21111>=$AD$7),--('PPV Data'!$I$8:$I$21111<=$AD$8),--('PPV Data'!$H$8:$H$21111="JP"),--('PPV Data'!$Q$8:$Q$21111=AA8:AA41))

PPV Data sheet cells
"I" are dates
"H" is the program element
"Q" is the contract number

so in my report worksheet I have cells to enter the contract numbers I want included, this could range from 1 contract up to 33 contracts. I want the report to tell me, from the contract numbers entered in cells AA8 through AA41, There are X number of cells with the JP program element between the two dates entered.

If I use only a single contract number and reference only a single cell in this part of the formula ---('PPV Data'!$Q$8:$Q$21111=AA8:AA41) I have no problem.

Ideas?

On all versions...

Please Login or Register  to view this content.

Another option is a SumProduct formula with CountIfs, as posted in another reply.

Aladin Akyurek,
please don't quote whole posts -- it's just clutter.
If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

Thanks for the Ideas. They don't seem to calculate what I need.

Here is a sample worksheet. I put everything on one sheet for the sample. In my workbook it's on two different tabs, but this will work well to play with.

Wonderful! You guys just made my day! Thanks a Bunch!