Ignore Blanks with this formula

[Agbarker]

I am banging my head against a brick wall, but i cant for the life of me work out a way to ignore blanks and only take a number as an answer

=SUMPRODUCT(('Group Stage Fixtures'!$B$50:$B$85='Group Stage Results'!B5)*('Group Stage Fixtures'!$C$50:$C$85>'Group Stage Fixtures'!$E$50:$E$85))

its this bit which is blank = Group Stage Fixtures'!$C$50:$C$85>'Group Stage Fixtures'!$E$50:$E$85))

if the cells are blank just return 0

any help would be highly appreciated on this

Many thanks

 

[Eric W]

Welcome to the MrExcel forum!

Try:

=SUMPRODUCT(('Group Stage Fixtures'!$B$50:$B$85='Group Stage Results'!B5)*('Group Stage Fixtures'!$C$50:$C$85+0>'Group Stage Fixtures'!$E$50:$E$85+0))

I wasn't sure which of the two ranges has the blanks so I put the +0 in both, but that may not be necessary. If you have other non-numeric data in that range, that could pose another problem.

 

[Agbarker]

Welcome to the MrExcel forum!

Try:

=SUMPRODUCT(('Group Stage Fixtures'!$B$50:$B$85='Group Stage Results'!B5)*('Group Stage Fixtures'!$C$50:$C$85+0>'Group Stage Fixtures'!$E$50:$E$85+0))

I wasn't sure which of the two ranges has the blanks so I put the +0 in both, but that may not be necessary. If you have other non-numeric data in that range, that could pose another problem.

Hi Eric

Thanks for your reply unfortunately that has not solved it. I am going to upload the spreadsheet so you can see what i am doing.

 

[Agbarker]

Ok so i cant upload the sheet but, here are some images

Fixtures image is where i am inputting the scores

then the group tables is where the formulas are

the formulas are as follows for the home stats in the middle

Home win = =SUMPRODUCT(('Group Stage Fixtures'!$B$50:$B$85='Group Stage Results'!B5)*('Group Stage Fixtures'!$C$50:$C$85>'Group Stage Fixtures'!$E$50:$E$85))
Home Draw = =SUMPRODUCT(('Group Stage Fixtures'!$B$50:$B$85=B5)*('Group Stage Fixtures'!$C$50:$C$85='Group Stage Fixtures'!$E$50:$E$85))
Home Loss = =SUMPRODUCT(('Group Stage Fixtures'!$B$50:$B$85='Group Stage Results'!B5)*('Group Stage Fixtures'!$C$50:$C$85<'Group Stage Fixtures'!$E$50:$E$85))

I a having issues with the middle one as it is counting blank cells as a draw

Any ideas would be great, happy to keep playing around with it

Many thanks

 

[Eric W]

Maybe:

=SUMPRODUCT(('Group Stage Fixtures'!$B$50:$B$85=B5)*('Group Stage Fixtures'!$C$50:$C$85='Group Stage Fixtures'!$E$50:$E$85)*('Group Stage Fixtures'!$C$50:$C$85<>""))

 

[Agbarker]

YES YES YES - thats done it! Genius! thank you very much!

 

[Eric W]

Happy to help! Thanks for the feedback!