Sumproduct formula issue

I am having an issue with my sumproduct formula.

I have attached a copy of my spreadsheet. I am using the sumproduct as my form for reporting up to the minute results.

In the Cascade Actual Summary tab, I used the sumproduct formula to report results from the cascade detail tab. When someone puts in the proper criteria it is supposed to automatically fill out my Summary tab to show what has been completed. In the far right 2 columns you will see under completed the new column and the % complete. This is the sumproduct formula that isn't working. It should be counting these but it isn't.

I was hoping for another set of eyes.

Thank you in advance.

Currently the formulas in F3 down will always return zero because you are trying to count when column D = 2800 and when column D = 4000. The way the formula is written you'll only get a count when both of these conditions is TRUE, which can't happen.

Also as the 4000 and 2800 entries are numeric these should have quotes. Perhaps you want something like

=SUMPRODUCT(('Cascade Detail'!$C$2:$C$7004=A3)*ISNUMBER(MATCH('Cascade Detail'!$D$2:$D$7004,{2800,4000},0))*('Cascade Detail'!$H$2:$H$7004<>"")*('Cascade Detail'!$I$2:$I$7004<>""))

In F3 this will count when column C is "Birmingham", column D is 2800 or 4000 and both column H and column I are not empty. IS that what you require?

Yes, that is what I am looking for.

Let me try the code you put out there.

It didn't work for me for some reason. I formatted those columns to numbers but not working still.

Are you using the formula I posted? When I said the numbers should have quotes I meant to say shouldn't, so the formula I posted was correct

Are you trying the formula with the data you posted? As your data stands I don't think you have any rows that fulfil the criteria so it will return zero.

Yeah, I put it in exactly how you had it without the quotes around the numbers.

I then put in some dummy data in the columns it counts to make it count and it didn't work.