Help with sumif formula - year to date

**MCunningham** · 01-04-2013, 04:30 PM

I'm completely stuck on this one and hoping someone can help.

I have 2 relevant columns with about 20,000 rows. The first column (A), has data formatted as

FY(2 digit fiscal year) - P(one or two digit period) - W(1 digit week)

Column B has values

I current have no problem with sumif-ing the values for a given week, period, or year. What I need to do is sum for year to date. For instance, if I want year to date for FY13, P2, then I want all values in column B where column A contains either:

FY13 - P1 - W* -- (* indicating any), or
FY13 - P2 - W*

Is there a way to put a less than or equal to in the lookup criteria within the text string?

Beers to whomsoever can solve this (new job, want to impress!)

**Ron Coderre** · 01-04-2013, 04:38 PM

See if this helps:

Sum for FY13, P2:
=SUMIF(A2:A50,"fy13 - P2*",B2:B50)

YTD sum through P2 for FY13
=SUMIF(A2:A50,"<=fy13 - P2*",B2:B50)

Is that something you can work with?

**oeldere** · 01-04-2013, 05:10 PM

Or with an pivot table.

You don't add an excel file, so i can't show it to you.

**MCunningham** · 01-04-2013, 08:48 PM

I wound up going with the pivot table simply because it allowed me more flexibility. Those active x buttons and macros are nice, but elegance is divine!

Thank you both so much for your help!