SUMPRODUCT formula not working

Hi,
I have been using the following formula successfully just by changing the names. For example I can change it from "Jennifer" to "Michael" to obtain my data successfully.

=SUMPRODUCT(--('Paste Query Results Here'!$A:$A="Jennifer"),--('Paste Query Results Here'!$D:$D<>"NOT"),--('Paste Query Results Here'!$K:$K<>"K12"),--ISNUMBER(MATCH('Paste Query Results Here'!$D:$D,{"ADMIT","CANC","DENY","PEND","STU","WDRAW","CANCX","CANPX"},0)))

However, I need to be able to change my formula to pull data for everyone else than my main 8 names. I tried this but it didn't work:

=SUMPRODUCT(--('Paste Query Results Here'!$A:$A<>"Michael","Reggie","Jennifer","Hortencia","Matthew","Amanda","Alex","Virginia),--('Paste Query Results Here'!$D:$D<>"NOT"),--('Paste Query Results Here'!$K:$K<>"K12"),--ISNUMBER(MATCH('Paste Query Results Here'!$D:$D,{"ADMIT","CANC","DENY","PEND","STU","WDRAW","CANCX","CANPX"},0)))


Please help! What am I doing wrong or missing? Your assistance would be greatly appreciated.

Try:

Please Login or Register  to view this content.

but be aware Sumproduct and full column references are a bad match (efficiency wise)

You can ape the existing MATCH approach but simply convert the outer test from ISNUMBER to ISNA

Please Login or Register  to view this content.

I'm pretty sure I've made the point previously but you should really be avoiding entire column references in SUMPRODUCT.

In the above (we must assume XL2007+ else it would not work) you're referencing/processing over 4 million cells...

Also (as previously mentioned I thin on other threads) the logic of your formula means the NOT test is superfluous given the subsequent MATCH test - removing it will save you a million cells
(ie it must be something other than NOT if it has passed the ISNUMBER(MATCH test...)

Maybe:

=SUMPRODUCT(--('Paste Query Results Here'!$A:$A<>{"Michael","Reggie","Jennifer","Hortencia","Matthew","Amanda","Alex","Virginia"}), --('Paste Query Results Here'!$D:$D<>"NOT"), --('Paste Query Results Here'!$K:$K<>"K12"), --ISNUMBER(MATCH('Paste Query Results Here'!$D:$D,{"ADMIT","CANC","DENY","PEND","STU","WDRAW","CANCX","CANPX"},0)))

What's causing the error? Do you have columns that have that error in them somewhere.

The formula itself should not cause that error.

You should only get #DIV/0! if any one of the cells referenced contains that specific underlying error. (Cols A,D,K)

You can wrap the SUMPRODUCT within an IFERROR test but I suspect you need to investigate the #DIV/0! at source first.

Maybe we should tag each other in from now on

Change

=P11/O11

to

=IF(N(O11),P11/O11,0)

or if you prefer

=IFERROR(P11/O11,0)

though note the latter is not backwards compatible (should that be a concern) and will also account for all error types - not just #DIV/0!

Originally Posted by MS Help

The N function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Value Required. The value you want converted.

N converts values listed in the following table.

A number - That number
A date, in one of the built-in date formats available in Microsoft Excel - The serial number of that date
TRUE - 1
FALSE - 0
An error value, such as #DIV/0! - The error value
Anything else - 0

In Excel 0 equates to FALSE, all other values are deemed TRUE - as such:

Please Login or Register  to view this content.

returns FALSE as

Please Login or Register  to view this content.

returns TRUE

Please Login or Register  to view this content.

in the above we take the value in O11 and est. numerical value of said value - if anything other than 0 the division is calculated else 0 is returned.

I suggested N to account for possibility of O11 containing say a space, formula Null etc... could equally use SUM.