Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct
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") |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct
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 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=511535 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct
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" wrote: 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 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=511535 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct
It shouldn't be too difficult to eliminate #N/A within your ranges, what formulas are you using that return #N/A? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=511535 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct
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 . Maybe we can work on this later on tomorrow morning if you are up to it. "daddylonglegs" wrote: It shouldn't be too difficult to eliminate #N/A within your ranges, what formulas are you using that return #N/A? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=511535 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |