Sumproduct not working

[blossomthe2nd]

Hi guys

Hope someone can help,

I have a sumproduct formula thats not working

=SUMPRODUCT(--('Master Result'!$E$1:$E$10000=A4),--('Master Result'!$P$1:$P$10000="John"))

The first part is looking for a Date on the Master results tab that matches the date in A4. ( I have tested this and this part of formula is working )

The second part should be looking for John in column P in the Master result spreadsheet. In this spreadsheet John is dervied from a Vlookup formula , and I can not get this part to work.

Can anyone please advise ? Would sumproduct on columns based on cells with formulas normally cause an issue ?

Thanks
A

 

[GlennUK]

No, there's nothing wrong with that, that I can see. Of course, if the result of the vlookup isn't "John" but " John" or "John ", then that is not a match.

 

[blossomthe2nd]

Hi GlennUK,

I have pasted "John" from the formula back onto the results of the Vlookup in the hopes of formating the all the same ( as in spaces caps etc) But I am still getting #N/A error.

Is it possible to do this search by a countif or something, there are mulitple criteria tho

Thanks

 

[GlennUK]

Ah, you didn't say that you were getting #N/A error. You need to surround your VLOOKUPs with IFs to suppress any #N/A results.

 

[blossomthe2nd]

Hi Glenn

The Vlookups are grand there are no N/As in that result , its the sumproduct that is giving the NAs when they are directed to the columns with the Vlookups !!!

Can sumproducts be used on columns that are formulas ?

 

[GlennUK]

Sumproducts can be used on columns that are formulas, but only if none of the cells has #N/A in them. Check your results, do a filter or a search and see where the #N/A is coming from.

 

[MrKowz]

Yes, they can. However, when a SUMPRODUCT returns a #N/A! error, that means there is a #N/A! error somewhere in the range(s) that the SUMPRODUCT is looking. Double check your data to ensure there are absolutely no errors in them.

 

[blossomthe2nd]

Glenn/Mr Kowz

I understand --- Takes me awhile but I get there in the end , !! I have taken your suggestions and surrounded by if(iserrors) and Sumproduct is working as expected , this learning curve is HUGE

Thanks

 

[scottylad2]

What version of excel are you using? if 2007 or later countifs will ignore the errors