Need help with a vertical and horizontal lookup

[Alex O]

I'm hitting a brick wall trying to come up with a formula that will lookup the value in Report!B2 and match it in 'Raw Data'!$A$3:$A$948 return the sum of values from 'Raw Data'!$A$3:$CQ$948 that correspond to the year value in C2.

Anyone have any suggestions?

Raw Data


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 60px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">CLT_ID</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">PLACEMENT_MONTH</TD><TD style="FONT-FAMILY: Calibri; FONT-SIZE: 11pt">ASSGN_PER_MONTH</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Jan-00</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Feb-00</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Mar-00</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Apr-00</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">May-00</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Jun-00</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Jul-00</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001479</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Apr-11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">13,389</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001479</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Sep-11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">12,142</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001479</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Sep-11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">4,604</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001479</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">Sep-00</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">5,076</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">0</TD></TR></TBODY></TABLE>

Report

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 94px"><COL style="WIDTH: 79px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Client ID#</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Calibri; FONT-SIZE: 11pt">01001494</TD><TD style="TEXT-ALIGN: right">2000</TD><TD style="TEXT-ALIGN: right">2001</TD><TD style="TEXT-ALIGN: right">2002</TD><TD style="TEXT-ALIGN: right">2003</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Total Placed</TD><TD style="TEXT-ALIGN: left">108,832</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Total Collected</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

 

[sheetspread]

You want a multiconditional sum (sumproduct or, for later excel versions, sumifs)

 

[Alex O]

I tried SUMIFS but it returned 0

 

[sheetspread]

If your only conditions are the client ID and year:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	CLT_ID	PLACEMENT_MONTH	ASSGN_PER_MONTH	Jan-00	Feb-00	Mar-00	Apr-00	May-00	Jun-00	Jul-00	Aug-00	Sep-00	Oct-00	Nov-00	Dec-00
3	1001479	11-Apr	13,389	10	20	30	40	50	60	70	80	90	100	110	120
4	1001479	11-Sep	12,142	10	20	30	40	50	60	70	80	90	100	110	120
5	1001478	11-Sep	4,604	10	20	30	40	50	60	70	80	90	100	110	120
6	1001479	Sep-00	5,076	10	20	30	40	50	60	70	80	90	100	110	120
Raw Data

Excel Workbook
	A	B	C	D	E	F
2	Client ID#	1001479	2000	2001	2002	2003
3	Total Placed	108,832	2340
4	Total Collected
Report

 

[Aladin Akyurek]

I tried SUMIFS but it returned 0

Also...

C3 on Report, control+shift+enter, not just enter, and copy across:

=SUM(
    IF('Raw Data'!$A$3:$A$6=$B$2,
    IF(YEAR('Raw Data'!$D$2:$CQ$2)=B$3,
        'Raw Data'!$D$3:$CQ$948))

which is robust against text values in the range to sum.

Note. SumIfs would not be applicable here.

 

[Alex O]

Thanks for both solutions....sometimes I simply over-think these things!

Regards

 

[Aladin Akyurek]

Thanks for both solutions....sometimes I simply over-think these things!

Regards

You are welcome. Thanks for providing us feedback.