| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT is not working
Hi all,
This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT is not working
here is the formula:
=SUMPRODUCT((('BAC - ABC'!B$5:B$711)=Comparison!B13)*(('BAC - ABC'!C$5:C$711)=Comparison!C13)*'BAC - ABC'!F$5:F$711) "Gladiator" wrote: Hi all, This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT is not working
Look in your ranges for "#VALUE!" if there is one of this in the range you
are applying SUMPRODUCT to, you will get this #VALUE! error. It has happen to me many times. "Gladiator" wrote: Hi all, This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT is not working
Not much detail for us to work with. Some examples of non-working SUMPRODUCT
formulas would have been helpful. My guess? More than likely you have specified ranges that are different sizes... all ranges (that are to be iterated over) must contain the same number of cells. -- Rick (MVP - Excel) "Gladiator" wrote in message ... Hi all, This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT is not working
I did not find '#VALUE!' error in the ranges.
"Ayo" wrote: Look in your ranges for "#VALUE!" if there is one of this in the range you are applying SUMPRODUCT to, you will get this #VALUE! error. It has happen to me many times. "Gladiator" wrote: Hi all, This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT is not working
You need to check each row from Row 5 to Row 711 in column B to F. Your
problem is withinthat range. "Gladiator" wrote: I did not find '#VALUE!' error in the ranges. "Ayo" wrote: Look in your ranges for "#VALUE!" if there is one of this in the range you are applying SUMPRODUCT to, you will get this #VALUE! error. It has happen to me many times. "Gladiator" wrote: Hi all, This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT is not working
Try this !
=SUMPRODUCT(('BAC - ABC'!B$5:B$711=Comparison!B13)*('BAC - ABC'!C$5:C$711=Comparison!C13)*('BAC - ABC'!F$5:F$711)) "Gladiator" wrote: I did not find '#VALUE!' error in the ranges. "Ayo" wrote: Look in your ranges for "#VALUE!" if there is one of this in the range you are applying SUMPRODUCT to, you will get this #VALUE! error. It has happen to me many times. "Gladiator" wrote: Hi all, This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT is not working
still not working, but I noticed one thing: when I retype the formula it
works fine, but when I lock the ranges it shows '#VALUE!' error and even if I remove the locks it still shows the error. "Ayo" wrote: Try this ! =SUMPRODUCT(('BAC - ABC'!B$5:B$711=Comparison!B13)*('BAC - ABC'!C$5:C$711=Comparison!C13)*('BAC - ABC'!F$5:F$711)) "Gladiator" wrote: I did not find '#VALUE!' error in the ranges. "Ayo" wrote: Look in your ranges for "#VALUE!" if there is one of this in the range you are applying SUMPRODUCT to, you will get this #VALUE! error. It has happen to me many times. "Gladiator" wrote: Hi all, This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT is not working
Ayo,
I found the error: one cell in the range had "..." in white font and when I removed that the SUMPRODUCT in all cells started woking fine. Thanks for your support thoug. "Gladiator" wrote: still not working, but I noticed one thing: when I retype the formula it works fine, but when I lock the ranges it shows '#VALUE!' error and even if I remove the locks it still shows the error. "Ayo" wrote: Try this ! =SUMPRODUCT(('BAC - ABC'!B$5:B$711=Comparison!B13)*('BAC - ABC'!C$5:C$711=Comparison!C13)*('BAC - ABC'!F$5:F$711)) "Gladiator" wrote: I did not find '#VALUE!' error in the ranges. "Ayo" wrote: Look in your ranges for "#VALUE!" if there is one of this in the range you are applying SUMPRODUCT to, you will get this #VALUE! error. It has happen to me many times. "Gladiator" wrote: Hi all, This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT is not working
I knew the problem was within the ranges. That is always the culprit. You
just have to find it. "Gladiator" wrote: Ayo, I found the error: one cell in the range had "..." in white font and when I removed that the SUMPRODUCT in all cells started woking fine. Thanks for your support thoug. "Gladiator" wrote: still not working, but I noticed one thing: when I retype the formula it works fine, but when I lock the ranges it shows '#VALUE!' error and even if I remove the locks it still shows the error. "Ayo" wrote: Try this ! =SUMPRODUCT(('BAC - ABC'!B$5:B$711=Comparison!B13)*('BAC - ABC'!C$5:C$711=Comparison!C13)*('BAC - ABC'!F$5:F$711)) "Gladiator" wrote: I did not find '#VALUE!' error in the ranges. "Ayo" wrote: Look in your ranges for "#VALUE!" if there is one of this in the range you are applying SUMPRODUCT to, you will get this #VALUE! error. It has happen to me many times. "Gladiator" wrote: Hi all, This formula sometimes works and sometimes shows #VALUE! error. Please anyone advice. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct not working | Excel Discussion (Misc queries) | |||
| SUMPRODUCT not working. | Excel Worksheet Functions | |||
| SUMPRODUCT Not Working | Excel Discussion (Misc queries) | |||
| SUMPRODUCT not working | Excel Discussion (Misc queries) | |||
| Sumproduct not working | Excel Worksheet Functions | |||
Linear Mode
