Counting numerous occurences, matching multiple criteria, only once

[Wooltrain]

Hello,

I have a large table that contains data for each indivdual item that is part of an order and tracks each stage of an order's progress over the course of a calendar month.
Amongst a lot of other data, the table records the order number, item code, expected date of dispatch (date), and (if the order was held) the relevant held type.

Because each row of the table is attributed to an item, all other variables appear multiple times (date, order, etc.) in other columns.
See the attached sample (data tab). The actual table is considerably larger so I've only represented a small example of the relevant columns.

I have a summary table in the workbook that I want to count the number of orders that were held on a given day and to list that count in the column relevant to that held type.
See the attached sample (summary tab).

I am currently using the following formula (taken from cell B2 on the summary tab):

=SUMPRODUCT(((Data!A:A<>"")/COUNTIF(Data!A:A,Data!A:A&""))*(Data!C:C=Summary!A2)*(Data!D:D="One"))

It works.
But when it is applied to my larger table (that contains more than 3500 rows) it takes too long for excel to run the calculation (sometimes up to 5 minutes) for just one date count.
When I apply the formula to every date and held type, it often throws back an error that Excel does not have the required resources to complete the calculation.
The calculation is critical to my monthly reporting but due to the lag of the formula it is taking far too long to produce the report.

I need a formula that performs the same function but does it more efficiently.
Please help.

I am running Excel 2007.

[FDibbins]

I will take a look at it for you. In the mean time, try to use a defined range rather then entire columns, something like...

=SUMPRODUCT(((Data!A1:A5000<>"")/COUNTIF(Data!A1:A5000,Data!A1:A5000&""))*(Data!C1:C5000=Summary!A2)*(Data!D1:D5000="One"))

You may be able to use countifS() for this, as well

[FDibbins]

OK try this, copied down and across (change the "One" as needed)...
=COUNTIFS(Data!$C$2:$C$5000,Summary!$A2,Data!$D$2:$D$5000,"One")

[Wooltrain]

Thanks FDibbins.

Thank you!
You're first suggestion did the job!

Your second solution was counting the total number of the held type for each order number on the specified date. I needed the formula to count these as just one occurence.

Appreciate the assistance.

[FDibbins]

Happy to help and thanks for the feedback