Formula Needed

[jim may]

Need formula in cell D4 that will search My sheets "JAN" through Content of cell D2 - "MAR" and
sum the total of all records in the sheets that Match the Acct# given in my Cell B4. Can someone assist me?



Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
1				Through:		Have
2				MAR	<< Variable assigned	Subsequent
3	Account NAME	Acct #	BUDGET	YTD SPEND		Contiguous
4	Pastor Salary	30	23,348.00	#REF!	<<< Formula Needed HERE	SheetNames
5					to return $1,200.00
6
7						JAN	Date pd	Check #	Requester:	Amount	Acct #	Explanation
8										100.00	30
9										100.00	30
10										50.00	28
11
12						FEB	Date pd	Check #	Requester:	Amount	Acct #	Explanation
13										200.00	30
14										200.00	30
15										60.00	27
16
17						MAR	Date pd	Check #	Requester:	Amount	Acct #	Explanation
18										300.00	30
19										300.00	30
20										70.00	26
21
22						APR	Date pd	Check #	Requester:	Amount	Acct #	Explanation
23										400.00	30
24										400.00	30
25										80.00	25
26

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D4 =SUMPRODUCT(SUMIF(INDIRECT("'"&#REF!&"'!E3:E60"),$B4,INDIRECT("'"&#REF!&"'!D3:D60"))) <tbody> </tbody>

<tbody>
</tbody>

 

[Misca]

I may have cheated a little bit but here's my suggestion:

First of all I added the From-variable to C2. Also, I created a named range called MONTHS where there's the sheet names in the same order as the actual sheets.

Having done this I came up with the following monster of an array formula (Must be entered with Ctrl + Shift + Enter):

=SUM(SUMIF(INDIRECT(INDEX(OFFSET(Months,MATCH(C2,Months,0)-1,0,MATCH(D2,Months,0)-MATCH(C2,Months,0)+1),0)&"!E2:E4"),B4,INDIRECT(INDEX(OFFSET(Months,MATCH(C2,Months,0)-1,0,MATCH(D2,Months,0)-MATCH(C2,Months,0)+1),0)&"!D2:D4")))

 

[jim may]

Misca,

Very Nice...

Thanks!!!

Jim