Sum Product - Pulling my hair out!

Sum Product - Pulling my hair out!

Didn't find your answer?

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.

avatar
By tinyme77
20th Nov 2014 12:07

Does the sumproduct work by itself (ignoring the IF statement)?

Thanks (0)
Replying to Tax Dragon:
avatar
By Rikos
20th Nov 2014 12:16

Yes all works fine with and without the IF formula

Thanks (1)
paddle steamer
By DJKL
20th Nov 2014 12:30

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.

Thanks (0)
avatar
By tinyme77
20th Nov 2014 13:00

Do you need to put brackets around the H30:T45 ?

Can you tell that I'm guessing?!

Thanks (0)
Replying to SteveHa:
avatar
By Rikos
20th Nov 2014 13:13

Unfortunately not.

The formula is working fine but it appears to be an issue with the data format. I have highlighted and formatted everything as number and even clicked in to each cell and pressed enter just to check it was updated.

Thanks (0)
pic
By jndavs
20th Nov 2014 13:19

Logically it should work

Does your range H30:T45 include any text, eg a space character?

 - try highlighting the empty areas and pressing delete.

 

Thanks (1)
Replying to Mr_awol:
avatar
By Rikos
20th Nov 2014 13:45

It Works!!

jndavs wrote:

Does your range H30:T45 include any text, eg a space character?

 - try highlighting the empty areas and pressing delete.

 

Genius!!!! 

I went through and pressed delete on every blank cell in my sum range and it now works!

If i ever see you i will buy you a drink.

Thanks (0)
Replying to Mr_awol:
By bro0010
20th Nov 2014 16:25

Another option

jndavs wrote:

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

www.onionrs.co.uk

Sage accounts in Excel to go

Thanks (1)
avatar
By martin.curtis
20th Nov 2014 13:19

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

 

 

Thanks (0)
Replying to rmillaree:
pic
By jndavs
20th Nov 2014 16:37

You are welcome

martin.curtis wrote:

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'.

Thanks (1)
Replying to Accountant A:
By bro0010
24th Nov 2014 12:38

A lookup for crosstabs

jndavs wrote:

martin.curtis wrote:

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.

Thanks (0)
avatar
By JJJAMES
20th Nov 2014 13:20

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
 

 

Thanks (0)
avatar
By Rikos
20th Nov 2014 13:46

Thank you everyone for your help and suggestions

Thanks (0)