Locking in a particular year

[Fred Houck]

Can someone show me how to write this formula so that it looks at a specific year to obtain a total?
=SUMPRODUCT(C$7:C$200,--(TEXT(A$7:A$200,"MMM")=A$207)) *A207 contains the formula =TEXT(TODAY(),"mmm")

The formula shown does provide the information needed for the current year, however I have the spread sheet set up with multiple year columns. Each separate years column will have the dates for that particular years expenses.

The formula shown needs to provide the current month data only for the current year. Currently every entry in 2022 should show in the total column cell for 2022 , and not show entry's that have a year of 2023 for example.

I have copies of all columns moving left to right for year 2022's thru 2030 and I need the totals to only show for the year and column the data is recorded. I can't simply use the NOW() or (TODAY() function as it will change the totals in every year's column to the current year as the years go by.

Thanks for any help offered!
Fred

[AliGW]

Try this:

=SUMPRODUCT(C$7:C$200,--(TEXT(A$7:A$200,"MMM YYYY")=A$207))

Change A207 to: =TEXT(TODAY(),"mmm yyyy")

[AliGW]

Alternatively, witout having to change A207:

=SUMPRODUCT(C$7:C$200,--(TEXT(A$7:A$200,"MMM YYYY")=A$207)*(YEAR(A$7:A$200)=2022))

Change year to suit.

If neither solution works for you, provide a sample workbook (you should always do this if you want quick response, anyway).

[Fred Houck]

Your suggestion only works for the current year not subsequent years going forward.
I've attached an example from my spreadsheet.

My attachment refers to A207 which is the correct cell for my actual spread sheet NOT the correct cell though in my attachment , it should say A22.

[AliGW]

A207 and E207 don't have formulae - where shall I find what I am supposed to be looking at?

Maybe try this (another guess):

=SUMPRODUCT(C$7:C$200,--(TEXT(A$7:A$200,"MMM YYYY")=A$207)*(YEAR(A$7:A$200)>=2022))

[AliGW]

Is this what you want???

=SUMPRODUCT(G$7:G$15,--(MONTH(E$7:E$15)=MONTH(E$22))*(YEAR(E$7:E$15)>=YEAR(E$22)))

The colour scheme in your workbook is not at all easy on the eyes - I would give serious consideration to changing it.

[Fred Houck]

Your formula worked for cell (C3) but not (A22) However I found a way to solve the problem by simply changing cell(A22) to (=A5). That cell is a drop down field to select a particular month and is setup to be the months for every specific year.

Thanks for you're help This forum is a life savor for me. I try out to use it till I've exhausted every possible thing I could think of trying but failed.

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[JohnTopley]

Suggested changes:

in B29

=SUMIFS(C$7:C$15,A$7:A$15,">="&A29,A$7:A$15,"<="&EOMONTH(A29,0))

Copy down

Copy to F29 (and other years).

Which figures are wrong ???