Three conditional sumproduct - what have I done wrong??

[ld2x07]

Hi all,

I have the following:

Please Login or Register  to view this content.

AH:AH - a range of dates. Coutning those in Novemeber
AJ:AJ - a range of subjects, counting those that match the subjects in the table
F:F - a range of other subjects that includes Near Miss

The code is coming back with NA

Any thoughts?

[Special-K]

There's probably a text value in one of those three columns
SUMPRODUCT works on ALL numbers only, if a cell contains text SUMPRODUCT will throw up an error.

Put in one condition at a time so you can isolate which column it is
If they have formulas in those columns it could be one of the cells is throwing up an error and causing your SUMPRODUCT error.

[ld2x07]

Oh dear the last condition is to look up a specific text among a column of text only values..

Would I use a CountIF for this instead then

[FDibbins]

SP should work OK with this.

As suggested, try 1 condition at a time and build up, see if you can find where it crashes.

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Special-K]

I may have made a mistake in my explanation.
I'm sure there's some kind of problem with SUMPRODUCT and text though
Perhaps someone could enlighten us on the specifics of SUMPRODUCT and text.

Or maybe it's when a formula returns #N/A ?

[ld2x07]

Hi Special-K,

Thanks for the suggestion, I have found that the error is in the date column that it is not seeing the dates as dates.

I have tried adding a column to change each date in column C into a 'date' by:

Please Login or Register  to view this content.

I even tried

Please Login or Register  to view this content.

But nothing seems to work!!

Any suggestions??

[boopathiraja]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[romperstomper]

If you are using Excel 2003 as your profile says, you cannot use entire column references in a SUMPRODUCT formula.

[newdoverman]

First, limit your ranges instead of having full column ranges. Excel 2003 can't handle the full columns in this formula.

Make sure that your dates in column AH are real Excel recognizable dates.

Having tried your formula with the above limitations, your formula will give you a count of the records that match all the criteria.