sumproduct

[Guest]

Please help someone. I have been trying to get a response on how to correct
this formula. I will always have some NA's in my columns that have the
criteria that I am summing on. I posted this question before but no one
seems to know the answer. Here is the formula that I have been working with:
=SUMPRODUCT(($L$7:$L$2585="F")*(I32:I2610=AP2627)*(AS32:AS2610)). I get a
result of NA but the formula shouldn't be looking at the NA's anyway. I
always thought that sumproduct ignored these but even if not, they are not
part of the criteria. Do I have this formula wrong. I am trying to sum
column AS if column L = "F" and column I equals whatever I have in cell AP267
(in the case an "11")

 

[daddylonglegs]

Rob,

In your previous thread you said you had #N/As in column I. SUMPRODUCT
doesn't ignore these.

You can try to eliminate these by altering the formula in I - what
formula is that? - or you can try a SUM/IF formula which needs to be
confirmed with CTRL+SHIFT+ENTER as I proposed here

http://www.excelforum.com/showthread.php?t=510776

 

[Guest]

Just wanted to say thanks. I tried the alternative but this sheet will be
used for a lot non savy excel people so that won't work. I am going to
attack the source. Thanks again.

 

[daddylonglegs]

It shouldn't be too difficult to eliminate #N/A within your ranges, what
formulas are you using that return #N/A?

 

[Guest]

I got rid of most of them. This is a huge workbook that I'm working on. It
is about 50 megabytes and counting. I think it is too much for Excel, but
that was the manager wanted to use. Anyway, a lot of the NA's are from
Vlookups, and this was done prior to my work on it. I have used the ISNA to
suppress most of them and the sumproduct is working now. Thanks again and
let's stay in touch. I was hoping that you might be able to help me with
another aspect of this workbook. I want to create a dialog box or a cell
that will prompt for information to sort on and then a macro will run. I'll
be working on that tomorrow. I've got some idea of how to work it but the
dialog box I have not attempted as yet. My email address at work is
(e-mail address removed). Maybe we can work on this later on tomorrow morning if
you are up to it.