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?

 

[Julesdude]

HI Jon, thanks for posting again. It's still not working for me though. I'm getting results returned that are well above what they need to be i.e. a 100 when the total in the column should only be 4

 

[Jon von der Heyden]

Can you post a sample please?

 

[Julesdude]

I'm not sure how I can paste the column and row references into my post like you did.

Basically it looks like:

A | B | C
1 WF | Description | [Formula here]
-
2 LF | Description | [Formula here]
-
3 PF | Description | [Formula here]
-
4
-
5 LF | PF | WF
-
6 2 | | 2
-
7
-
8 2 | | 2

So in C1 - this is where the formula goes. It needs to take whatever is in cell A1 and use this to look up the matching column name located in range A5:C5 (so it will be C5). Now it has the column reference, it needs to look through the entire column and come back to C1 with the total. So in this example it should be 4.

I'll use the same formula in C2 and C3, although it will be adapted (i.e. C2 forumula will use value in A2 and find match in the same range and find total in respective column.

Hope this makes sense. How do I do it?

 

[Julesdude]

Is there even a formula that can do this?

 

[Julesdude]

Bumpety bump

 

[Gerald Higgins]

If I understand post #13 correctly, this is certainly do-able with formulas, but I just want to make sure I understand it all correctly.

Are you saying that row 5, and only ever row 5, contains the codes to be looked up, such as LF, PF, WF and so on ?

And that in A1 you are specifying one of these codes ?

So, for example, if you plug "AZ" into A1, you need it to look for "AZ" on row 5, let's say that "AZ" is in column D, and then sum up column D.

Is that what you want ?
If yes, this is do-able with formulas.
Can you confirm please ?

 

[Haseeb Avarakkan]

Hello, Try

C1, copy down.

=IF(COUNTIF($5:$5,A1),SUM(INDEX($5:$65536,,MATCH(A1,$5:$5,0))),"")

 

[Julesdude]

Hi Gerald, yes that is correct for both. In the example, row 5 will always be the range to look in that contains a code, and that code to look for is specified in A1. C1 contains the source formula that will look to A1 to find that code in row 5.

This formula will also be repeated as you go down column 3. So C2 will look for code in A2 in row 5...and so forth.

Hope this makes sense. It's almost as hard to try and explain it

 

[Julesdude]

Hi there Haseeb, thanks for the formula! It works perfectly! However, I adjusted it slightly because it was giving me '0's when I didn't want these to appear.

In the end I have the following:

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

To conclude, this formula in cell A takes a cell's contents (B), searches for it in row range C, finds it at D, and then sums the total of the entire column D is in. If this total is greater than 0 then place that total value in cell A, else leave it blank.

Thanks all for your help!! I'll test some more and than try and work out what it all means and what it's doing!

 

[Julesdude]

Just a footnote to the above, I used the following formula to trap errors when there is no code to take at B

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