Sumifs, index & match

[Rtbrown]

I am building a financial model that has one tab that has hard-coded data and another tab that is a formatted monthly financial statement. I am trying to formula that looks at the current month and the ID # and SUMS all of the data on the input tab that has that specific ID #. I know this is possible through a combination of =SUMIFS(Index(input data range(Match but i am not sure how to structure this formula.

-Thank you in advance for any help!

				Jan-13	Feb-13	Mar-13
Balance Sheet
	ID #
	Ar1000	Cash
	Ar1010	Accounts Receivable
	Ar1020	Short Term Invstments		-	-	-
	Ar1030	Prepaid and Other Current Assets		-	-	-

<colgroup><col span="2"><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[Joyner]

Sounds like you can do that with just a SUMIFS formula. Can you provide some sample data and the layout you have?

 

[skywriter]

See if this works for you, if not maybe it will give you some ideas.

Note: The January 2014 etc in the top row are actually date January 1, 2014 etc., formatted to just show the month and year part. It is important that the entry in the cell be kept that way. You can custom format the entry to look however you want it to look, but the underlying value must stay the same.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1					Jan 2014	Feb 2014	Mar 2014
2	Balance Sheet								Ar1000	1/25/2014	Cash	$2,500.00
3		ID #							Ar1010	1/26/2014	Accounts Receivable	$3,200.00
4		Ar1000	Cash		$7,285.00	$7,458.00	$895.00		Ar1020	2/5/2014	Short Term Invstments	$4,578.00
5		Ar1010	Accounts Receivable		$3,200.00	$0.00	$444.00		Ar1020	3/1/2014	Prepaid and Other Current Assets	$9,562.00
6		Ar1010	Short Term Invstments		$96,523.00	$0.00	$365.00		Ar1020	2/1/2014	Cash	$4,125.00
7		Ar1020	Prepaid and Other Current Assets		$1,245.00	$0.00	$9,562.00		Ar1000	1/25/2014	Accounts Receivable	$85,412.00
8									Ar1010	3/2/2014	Short Term Invstments	$365.00
9									Ar1000	3/5/2014	Cash	$895.00
10									Ar1000	1/26/2014	Cash	$4,785.00
11									Ar1020	2/5/2014	Accounts Receivable	$45.00
12									Ar1000	3/1/2014	Short Term Invstments	$785.00
13									Ar1010	2/1/2014	Prepaid and Other Current Assets	$12.00
14									Ar1020	1/25/2014	Cash	$1,458.00
15									Ar1000	3/2/2014	Accounts Receivable	$1,247.00
16									Ar1010	1/25/2014	Short Term Invstments	$96,523.00
17									Ar1020	1/26/2014	Prepaid and Other Current Assets	$1,245.00
18									Ar1000	2/5/2014	Cash	$7,458.00
19									Ar1010	3/1/2014	Accounts Receivable	$444.00
20									Ar1020	2/1/2014	Short Term Invstments	$125.00
21									Ar1000	1/25/2014	Prepaid and Other Current Assets	$258.00
22									Ar1010	3/2/2014	Prepaid and Other Current Assets	$145.00
Sheet4