Sumproduct and zero's..help please

  • Thread starter Thread starter www.ttdown.com
  • Start date Start date
W

www.ttdown.com

Win98
Office XP Pro

I am using Sumproduct in an Excel S/S as follows:
"sumproduct(--(A4:AY4=$A$58:$AY$58))"

Works ok until I extend the S/S with another col.(AZ), then I get #N/A
for my final result on another S/S in the same workbook.

I can see the reason is due to AZ58 having a ZERO in that cell via a
"max" function, but unable to resolve.

Now if I enter any number greater than zero(project next weeks result)
in any cell from AZ4 to AZ56 and ammend the sumproduct to suit , then
it works. So I wish to enable the "sumproduct" to work beyond the
already entered cells and accomadate a larger range for future scores,
without me having to amend the range in the formulae.

I have been posting here this week on my worksheet problem and been
successful up to this point above.

Once again bare with me as I find it difficult to express the problem
technically as a novice.

TIA
 
Hi
does AZ58 contain a zero or an error value?
as the following formula:
sumproduct(--(A4:AZ4=$A$58:$AZ$58))
should work
 
Frank
As stated...AZ58 zero is a result of a "MAX" function
"=MAX(AZ4:AZ58)", because no entries are made in AZ Col. at this
stage.

HTH
 
Hi
but then the formula from below should work. If there are nor erros in
the range A4:Z4 and A58:AZ58 SUMRODUCT should not produce an error
 
I am missing something out here FRANK.?
What function/s would I use for the same task, but all cells begin
blank...then when I enter the first date/scores the function kicks in.

ttd
 
Hi
try
=IF(COUNTA(A$:AZ4)>0,sumproduct(--(A4:AZ4=$A$58:$AZ$58)),"")

--
Regards
Frank Kabel
Frankfurt, Germany

I am missing something out here FRANK.?
What function/s would I use for the same task, but all cells begin
blank...then when I enter the first date/scores the function kicks in.
Click to expand...
error
 
I used this new formula in a cell which had the previous formula
without the Counta, but the result was 3, and the correct result
should be 2.(wins)

If I delete the very last Col in the range(AZ) I then get correct
result of 2.

In the cells where data will be entered as the games are played, I
have a conditional format in use....which highlights the HIGHEST score
in a Col....Wonder if that is affecting the result??

I just corrected the A$ to A4 in your new formula(Counta).....not
trying to be clever but to save you pointing it out.

Appreciate your patience Frank.

ttd (terry)
 
Back
Top