SumProduct formula returns #VALUE Error

I've been working on this issue for a couple of days and no amount of further Googling is going to help me.

I'm not sure what I'm doing wrong with this formula in Excel 2003. I figured out how to do it in Excel 2007 using the SUMIFS statement, but alas Excel 2003 won't take a SUMIFS statement.

I have data in a column, J of Worksheet B that I need to add based on 2 conditions. The first conditions are in cells AJ6 to 8 on Worksheet A. The second condition must be matched from Column A of Worksheet B to the same condition in Column F on Worksheet A.

Here is the formula that I put together that isn't working.

Please Login or Register  to view this content.

Thanks in advance for your assistance.

Chris

Based on my interpretation of your original formula try:

Please Login or Register  to view this content.

The formula you have is checking 'Worksheet A'!$J$4:J200 to see if it has either of 'Worksheet A'!$AC$6, 'Worksheet A'!$AC$7 or 'Worksheet A'!$AC$7 and at the same time 'Worksheet A'!$F$4:$F$200 must equal 'Worksheet B'!$A5

This is supposed to count those that match that criteria... is that what you want?


Your description of the problem and the formula you show don't match....

Also, you must eliminate all the double quotes... they are not used in this formula.

DonkeyOte
The values in AC6 through AC8 are the following text so the ISNUMBER won't help.
On Hold
Complete
Cancelled

Irrelevant... ISNUMBER(MATCH tests that your values appear in the range, if they do the MATCH will return the index position (a number), if not it will return an Error (NA) ... ISNUMBER thus validates the value exists in the range.

Edit: Your columns also appear to have changed from before...

Perhaps?

Please Login or Register  to view this content.

you have to change the reference in Worksheet B!A5?

Perhaps:

Please Login or Register  to view this content.

This is a variant on NBVC using ISNUMBER(MATCH( as opposed to OR (+) operator.

Please test prior to dismissing.