Hello all,
I am about to throw my laptop out of the window!
For some reason my Sumproduct formula will not work on a certain set of data. The formula is as follows =IF(B3="Square",SUMPRODUCT((E30:E45=B5)*(G30:G45=B7)*(H29:T29=B9)*H30:T45)+0,0)
I know that the formula works as i have tested on other data. The data that i am using is formatted as number but whenever i try the above formula on it i get #VALUE
Why??????
Data is as follows:
mm | 0.91 | 1.02 | 1.22 | 1.42 | 1.50 | 1.63 | 1.83 | 2.03 | 2.34 | 2.50 | 2.64 | 3.00 | 3.25 | ||
12.70 | x | 12.70 | |||||||||||||
15.88 | x | 15.88 | 41.06 | 47.22 | 56.43 | ||||||||||
19.05 | x | 19.05 | 47.24 | 54.35 | 65.65 | ||||||||||
20.00 | x | 20.00 | 54.77 | ||||||||||||
22.22 | x | 22.22 | 52.39 | 60.74 | 74.46 | ||||||||||
25.00 | x | 25.00 | 58.81 | 69.14 | 84.53 | 99.30 | 129.14 | ||||||||
25.40 | x | 25.40 | 58.81 | 68.55 | 83.77 | 101.08 | 131.70 | ||||||||
28.58 | x | 28.58 | 65.35 | 76.51 | |||||||||||
30.00 | x | 30.00 | 80.53 | 100.17 | 119.98 | 151.82 | |||||||||
31.75 | x | 31.75 | 71.63 | 84.91 | 104.84 | ||||||||||
34.92 | x | 34.92 | |||||||||||||
35.00 | x | 35.00 | |||||||||||||
38.10 | x | 38.10 | 84.74 | 99.98 | 124.93 | 147.96 | |||||||||
45.00 | x | 45.00 | |||||||||||||
50.00 | x | 50.00 | 130.35 | 163.55 | 263.52 | ||||||||||
50.80 | x | 50.80 | 130.35 | 163.55 |
If someone can help I will be forever grateful!
Replies (13)
Please login or register to join the discussion.
Is if not a conditional?
As the IF function is a conditional why can i not spot the resultant if the condition not met. you have the If B3= "Square", formula, but where is the else if it does not?
Or am I being really stupid (not that hot with excel)
EDIT I am losing my eyesight, now seen the ,0 at end, ignore, sorry.
Logically it should work
Does your range H30:T45 include any text, eg a space character?
- try highlighting the empty areas and pressing delete.
Another option
Does your range H30:T45 include any text, eg a space character?
- try highlighting the empty areas and pressing delete.
I've come across users who "delete" cell contents by hitting the space-bar more times than I care to remember.
Another technique I've found useful is to select the entire area I want to check (say, the whole worksheet) and then press F5 (Edit | Goto), click on Special... and then choose the Blanks radio button. The cell you are looking for is the one that appears to be empty but isn't highlighted. Delete the contents of that cell.
Sometimes the converse selection is easier to see: press F5 (Edit | Goto), click on Special... and then choose the Constants radio button and the Text tick box. The cell you are looking for will be highlighted but appears to be empty. Delete the contents of that cell.
Same as jndavs solution really, but avoids having to select lots of non-contiguous empty areas sometimes.
Warm regards,
Ian
Onion Reporting Software Ltd
Sage accounts in Excel to go
the array sizes do not match
The arrays need to be of the same size
E30:E45
G30:G45
H29:T29
H30:T45
The first two are fine but because the other two are smaller/larger the error return is #Value
You are welcome
The arrays need to be of the same size
E30:E45
G30:G45
H29:T29
H30:T45
The first two are fine but because the other two are smaller/larger the error return is #Value
Actually, this is just one array!
It evaluates to (0|1)*(0|1)*(0|1)*H30:T45 or more simply (0|1)*H30:T45
In this case it's a clever way of indexing the table, the 'E*G' gives you the row and '*H:T' the column. You could probably do away with 'G'.
A lookup for crosstabs
The arrays need to be of the same size
E30:E45
G30:G45
H29:T29
H30:T45
The first two are fine but because the other two are smaller/larger the error return is #Value
Actually, this is just one array!
It evaluates to (0|1)*(0|1)*(0|1)*H30:T45 or more simply (0|1)*H30:T45
In this case it's a clever way of indexing the table, the 'E*G' gives you the row and '*H:T' the column. You could probably do away with 'G'.
@jndavs, Thank you for the above comment. Your observation had completely escaped me until I looked at it more closely. I now realise that what you end up with is an array of 0s and 1s with the same dimensions as H30:T45 that, when applied to H30:T45, returns the value of interest. In effect, this gives a lookup capability for numeric values in a crosstab. I've no doubt I'll find many uses for this in future.
@rikos, thanks for sharing the original formula - neat solution.
Try "TYPE"
Sometimes Vlookup formula doesn't work - if it all looks ok it's often that there are different "TYPES" of date eg some text & some number - try using the value formula for find out if all data is the same type (simply formating it all as general doesn't work)
Returns the type of value. Use TYPE when the behavior of another function depends on the type of value in a particular cell.
Syntax
TYPE(value)
Value can be any Microsoft Excel value, such as a number, text, logical value, and so on.
IF VALUE ISTYPE RETURNSNumber1Text2Logical value4Error value16Array64