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?

 

[jonathan92591]

Thank you for responding. I've tried to do this with date and find the small and large but I can't make it work. If you have the time, could you post an example. I would be very grateful. Thank you.

JT

 

[Jon von der Heyden]

Or rather can you show up what you have?

 

[jonathan92591]

Or rather can you show up what you have?

Sorry for getting back so late. Yes, that makes sense. Ok.

--

Excel or Access version: Excel 2010

Computer operating system: Windows XP

Sample data:

Sheet 1 (front)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	SOS/ASS100321
2	SOS/ASS100321D
3	SOS/ASS100321S
4	Dates		*	*	Out	Month	Purchases	% of Total	Rank	*	*
5	Start	5/15/2009	3933330	*	11	May-09	2	1%	9	*	*
6	End	3/22/2010	*	*	10	Jun-09	10	5%	7	*	*
SOSASS100321

Sheet 2 (back)

Excel Workbook
	IG	IH	II	IJ	IK	IL
1	***/SOS110320D	***/SOS110320S	***/SOS110703D	***/SOS110703S	***/SOS111023D	***/SOS111023S
2	6/3/2010	1	5/12/2010	1	3/3/2011	3
3	6/23/2010	1	5/18/2010	6	3/7/2011	3
4	7/5/2010	1	5/21/2010	1	3/10/2011	2
5	7/6/2010	2	6/4/2010	2	3/11/2011	1
6	7/7/2010	1	6/7/2010	2	3/15/2011	1
7	7/15/2010	1	6/10/2010	2	3/25/2011	1
8	7/16/2010	1	6/23/2010	2	3/28/2011	1
9	7/30/2010	1	6/28/2010	2	3/29/2011	1
10	9/14/2010	5	7/6/2010	1	3/30/2011	2
11	9/20/2010	1	7/15/2010	2	3/31/2011	2
12	9/23/2010	2	7/16/2010	2	4/4/2011	1
13	9/27/2010	1	7/19/2010	2	4/19/2011	2
Data

Formula(s) right now: = IF(COUNTIF(Data!$1:$1,SOSASS100321!$A$2),SUM(INDEX(Data!$1:$1048576,0,MATCH(SOSASS100321!$A$2,Data!$1:$1,0))),"")

Current result(s): 3933330

My goal: To have the small and large function look at an ID, then go to the backsheet and find the column and return an answer.

Error message: No error message

How error occurred: No error message

Generated in: Excel

Thank you.

JT

 

[Jon von der Heyden]

Does "SOS/ASS100321D" exist in row 1 in your data sheet?

 

[jonathan92591]

Does "SOS/ASS100321D" exist in row 1 in your data sheet?

Sir yes sir.

JT

 

[Jon von der Heyden]

Smallest:

= IF(COUNTIF(Data!$1:$1,SOSASS100321!$A$2),MIN(INDEX(Data!$1:$1048576,0,MATCH(SOSASS100321!$A$2,Data!$1:$1,0))),"")

or:

= IF(COUNTIF(Data!$1:$1,SOSASS100321!$A$2),SMALL(INDEX(Data!$1:$1048576,0,MATCH(SOSASS100321!$A$2,Data!$1:$1,0)),1),"")

<hr>
Largest:

= IF(COUNTIF(Data!$1:$1,SOSASS100321!$A$2),MAX(INDEX(Data!$1:$1048576,0,MATCH(SOSASS100321!$A$2,Data!$1:$1,0))),"")

or:

= IF(COUNTIF(Data!$1:$1,SOSASS100321!$A$2),LARGE(INDEX(Data!$1:$1048576,0,MATCH(SOSASS100321!$A$2,Data!$1:$1,0)),1),"")

 

[jonathan92591]

Thank you. I am working on this task all day today. As soon as I can, I will test your solutions. I sincerely appreciate your time and energy on this. You rock kind sir.

JT