HLOOKUP to return entire sum of column

[Julesdude]

Hi there. I need help trying to create a simple(ish) forumula. One cell needs to have a formula that uses the HLOOKUP function for range V3:AK3. But what I want it to return is not the value of a particular cell but the sum of the entire column that is identified in this HLOOKUP range.

I also need to trap any errors so if a value is 0 or is an error, to just display a blank.

Can anyone please help?

 

[TonyMJ]

An alternative approach could be to use SUMPRODUCT something like:

=SUMPRODUCT((A5:C5=A1)*(A6:C30000))

(A5:C5=A1) being your criteria and range
(A6:C30000) being the sum range

Results are the same, just to me looks neater and easier to understand.

You can use just row references but my laptop gets a bit funky if I calc on entire rows in 2007.

On returning blank when the returned value =0 it would be more efficient and quicker to calculate if you did this via the cells number format rather than repeating the formula. This is on the presumption you just don't want to see the zero rather than doing some additional calcs(ISBLANK formula or something).

 

[Julesdude]

Hi Tony, that looks a lot neater. However, in the range A6:C30000 would that total everything in that range or just the target individual column?

 

[TonyMJ]

It will return just the column that meets the criteria.

Basically if the criteria is met then it returns a 1 if not then 0 and if we multiply it by the corresponding cell value it will end up only summing those which meet the criteria.

So if A5 = A1 then the formula looks like =SUMPRODUCT(1*(A6*C30000)) then as Column 'A' meets the criteria it will check all the cells from A6 to A30000 individually. So if A6 = 20 then it becomes =SUMPRODUCT(1*20) and it does this for all the cells in the range.

It then gives you the ability to go 3D and sum based on another criteria in rows. So say you had years which varied in column Z you could modify the formula to be =SUMPRODUCT((A5:C5=A1)*(Z6:Z30000=2010)*(A6:C30000))
Now you get a sum that meets you original plus those that have 2010 against the row.

Apologies for the long response and if has confused things further.

 

[Julesdude]

Thanks Tony. I've adapted to my specific range and it works surprisingly well. It seems a bit slower to work out the formula than before, but can't be sure as my work computer is about 7 years old anyway!

I guess the best way to iron out a '0' return is to put:

=IF(SUMPRODUCT(($J$3:$DY$3=B44)*($J$4:$DY$30000)) > 0, SUMPRODUCT(($J$3:$DY$3=B44)*($J$4:$DY$30000)), "")

...but still, it means calculating twice, but I can't see a way around that.

 

[TonyMJ]

As I mentioned earlier if all you want to do is not display the zero then the best way would be to use cell formatting.

When formatting the number you probably choose one of standard Number/Accounting/Currency/Date/Percent etc...

The best way to stop zero's displaying is to use a custom format - all you need to know is the structure and some of the basic codes.

So you have four different cell results which Excel can format differently and the convention in the input box is Positive Value ; Negative Value ; Zero Value ; Text and you would separate them with semicolons ";".

So very basically to get rid of the zero you could put in 0;0;; As the third (zero) part is blank it wont display any zero values.

There are many articles on the net (like: http://www.ozgrid.com/Excel/CustomFormats.htm) or even the F1 isn't too bad on this (for a change) for various options.

SUMPRODUCT does come with a health warning that it can be quite calc intensive so use sparingly and I certainly wouldn't recommend copy into 100's of cells.

 

[Julesdude]

Thanks Tony, it turned out that because I have to have the SUMPRODUCT formula nearly 100 times, it dramatically slows things down. In the end I opted for the following formula:

=IF(ISERROR(SUM(INDEX($3:$65536,,MATCH(B44,$3:$3,0))) > 0), "", IF(SUM(INDEX($3:$65536,,MATCH(B44,$3:$3,0))) > 0,SUM(INDEX($3:$65536,,MATCH(B44,$3:$3,0))),""))

which takes out both the error and any 0s. I know it's not great as the calculation is done 3 times in the same formula, but even then it's still much faster. I like the SUMPRODUCT though and will probably look at using it again at some point.

I'll also look into CF. I knew it would be easy to just format out the 0s, which I'd normally do. It's just a personal thing really that I find it only hides the 0s from view rather than gets rid of them. Also with CF I find it more hidden if formating is going on in the cell whereas a formula is I guess more visible and on the surface. As I say it's just a personal thing really.

 

[TonyMJ]

My pleasure.

SUMPRODUCT is good for creating summary reports without need for pivot tables but not so good for doing mass calcs. You could create a UDF to do the same but much faster, but that depends on your VBA abilities.

In Excel 2007 + you get the very useful IFERROR function which would cut the formula down and speed it up.

On CF I have noticed over many years people replacing zero's with various characters and then wondered why calculations off it don't then work. But if it works for you...

 

[Haseeb Avarakkan]

Try this,

This will give you 0 if if the answer is error.

=LOOKUP(1E+100,CHOOSE({1,2},0,SUM(INDEX($3:$65536,,MATCH(B44,$3:$3,0)))))

Then use CF or cell format as 0;0;; to hide the zeros.

 

[jonathan92591]

Hello,

Does your solution only work for sum? I'm trying to do something similar but for "small" and "large" and "average." Thank you.

JT

Apologies, error in my previous sample. Please refer to this one:

Excel Workbook
	A	B	C	D	E
1	Lookup Val	abc
2	Result	5180
3
4	a	ab	abc	abcd	abcde
5	499	360	240	407	843
6	423	955	435	956	312
7	257	254	364	456	932
8	63	564	102	372	747
9	630	592	801	484	127
10	20	743	43	596	972
11	649	333	989	536	700
12	780	393	542	138	617
13	447	610	275	142	248
14	155	96	451	339	734
15	626	938	151	602	360
16	588	581	787	646	501
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=IF(COUNTIF($A$4:$E$4,$B$1),SUM(INDEX($A:$E,0,MATCH($B$1,$A$4:$E$4,0))),"")

 

[Jon von der Heyden]

It should work with all those functions.