Formula to match text in Column A of same row with data on spreadsheet matching month in cell C1 - return sum

dbeard

New Member
Joined
May 16, 2016
Messages
6
I'm trying to eliminate the exact cell references in order to make it easier to put the formula in 400 cells.

Dragging down/across is creating issues.

My current formulas
C2

=SUM(IF(January!B:B=OFFSET(C2, 0, -2), January!A:A,0))

D2
=SUM(IF(February!B:B=OFFSET(D2, 0, -3), February!A:A,0))

D3
=SUM(IF(February!B:B=OFFSET(D3, 0, -3), February!A:A,0))

I'm trying to have it match the month in Row 1 with the spreadsheet of the same name - and sum up the Expenses from the monthly sheet by matching up the Expense Categories on Column B (on the month sheet) with the Category in the 1st row of the Yearly sheet.


HTML:
[RANGE=cls:xl2bb-100][XR][XH=cs:15]Book1[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][XH]I[/XH][XH]J[/XH][XH]K[/XH][XH]L[/XH][XH]M[/XH][XH]N[/XH][/XR][XR][XH]1[/XH][XD=h:r][/XD][XD=h:c]YEARLY TOTAL[/XD][XD=h:c]JANUARY[/XD][XD=h:c]FEBRUARY[/XD][XD=h:c]MARCH[/XD][XD=h:c]APRIL[/XD][XD=h:c]MAY[/XD][XD=h:c]JUNE[/XD][XD=h:c]JULY[/XD][XD=h:c]AUGUST[/XD][XD=h:c]SEPTEMBER[/XD][XD=h:c]OCTOBER[/XD][XD=h:c]NOVEMBER[/XD][XD=h:c]DECEMBER[/XD][/XR][XR][XH]2[/XH][XD=h:l]AHIC[/XD][XD=h:c][/XD][XD=h:c]$ 1,040.00[/XD][XD=h:c]$ 465.00[/XD][XD=h:c]$ 465.00[/XD][XD=h:c]$ 465.00[/XD][XD=h:c]$ 465.00[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][/XR][XR][XH]3[/XH][XD=h:l]Business Entertainment[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]4[/XH][XD=h:l]Client Development[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]5[/XH][XD=h:l]Client Gifts[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]6[/XH][XD=h:l]Computer & Phone Equipment[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]7[/XH][XD=h:l]Continuing Education[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]8[/XH][XD=h:l]Contributions - Campaign/Political[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]9[/XH][XD=h:l]Contributions - Charitable[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]10[/XH][XD=h:l]Crested Butte Property Expense /
Research[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]11[/XH][XD=h:l]Dues (KC, etc.)[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]12[/XH][XD=h:l]Entity Formation Fees (Secretary of
State)[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]13[/XH][XD=h:l]Furniture & Fixtures & iPhone[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]14[/XH][XD=h:l]Health Insurance[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]15[/XH][XD=h:l]Healthcare[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]16[/XH][XD=h:l]Gas & Mileage[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]17[/XH][XD=h:l]Legal Fees[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]18[/XH][XD=h:l]LOC (Annual Charge)[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]19[/XH][XD=h:l]Meals[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]20[/XH][XD=h:l]Medical[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]21[/XH][XD=h:l]Merrill Lynch CMA Annual Fee[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]22[/XH][XD=h:l]Office Supplies[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]23[/XH][XD=h:l]Option Fees[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]24[/XH][XD=h:l]Parking[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]25[/XH][XD=h:l]Postage[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]26[/XH][XD=h:l]Professional Services[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]27[/XH][XD=h:l]Professional Taxes, Fees, & Dues[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]28[/XH][XD=h:l]Property Taxes[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]29[/XH][XD=h:l]Sales Tax[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]30[/XH][XD=h:l]Travel (incl Hotel)[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]31[/XH][XD=h:l]TVD[/XD][XD=h:c][/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]32[/XH][XD=h:r][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]33[/XH][XD=h:r|fw:b]TOTAL EXPENSES:[/XD][XD=h:c|fw:b][/XD][XD=h:c]$ 1,040.00[/XD][XD=h:c]$ 465.00[/XD][XD=h:c]$ 465.00[/XD][XD=h:c]$ 465.00[/XD][XD=h:c]$ 465.00[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][XD=h:c]$ -[/XD][/XR][XR][XH=cs:15][RANGE][XR][XD]Yearly[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Can't edit posts - and html tags didn't work - so I'm replying.

HTML:
ABCDEFGH1YEARLY TOTAL JANUARY  FEBRUARY  MARCH  APRIL  MAY  JUNE 2AHIC $ 1,040.00  $ 465.00  $ 465.00  $ 465.00  $ 465.00  $ - 3Business Entertainment $ -  $ - 4Client Development $ -  $ - 5Client Gifts $ -  $ - 6Computer & Phone Equipment  $ -  $ - 7Continuing Education $ -  $ - 8Contributions - Campaign/Political $ -  $ - 9Contributions - Charitable $ -  $ - 10Crested Butte Property Expense / Research $ -  $ - 11Dues (KC, etc.) $ -  $ - 12Entity Formation Fees (Secretary of State) $ -  $ - 13Furniture & Fixtures & iPhone $ -  $ - 14Health Insurance $ -  $ - 15Healthcare $ -  $ - 16Gas & Mileage $ -  $ - 17Legal Fees $ -  $ - 18LOC (Annual Charge) $ -  $ - 19Meals $ -  $ - 20Medical $ -  $ - 21Merrill Lynch CMA Annual Fee $ -  $ - 22Office Supplies $ -  $ - 23Option Fees $ -  $ - 24Parking $ -  $ - 25Postage $ -  $ - 26Professional  Services $ -  $ - 27Professional Taxes, Fees, & Dues $ -  $ - 28Property Taxes $ -  $ - 29Sales Tax $ -  $ - 30Travel (incl Hotel) $ -  $ - 31TVD $ -  $ - 3233TOTAL EXPENSES: $ 1,040.00  $ 465.00  $ 465.00  $ 465.00  $ 465.00  $ - [CENTER][COLOR=#161120][B]Yearly[/B][/COLOR][/CENTER]
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top