SUMIFS and Date Criteria (Month Year etc...) but without helper column?

**JungleJme** · 01-16-2013, 06:54 AM

OK, i have range in a report that sums values on a seperate document dependent on certain criteria.

Now, i can't have helper columns on the source data because the source document will be overwritten every month.

My report has a year field and a month field, that increments each month and form the limit for the report, so we want to sum values where the year = the report year and the month is <= the report's month.

I have a feeling that SUMIFs may be the wrong way to go and SUMPRODUCT may be required, but i have man flu and my brain is refusing to engage with anything beyond remembering my own name....

The attached is an .xls but i am using Excel 2010.

**Fotis1991** · 01-16-2013, 07:07 AM

Why your expected result for year 2012, month=12 and "Me", is 80 ?? B9 = November of 2012. not December.

=SUMPRODUCT((YEAR($B$5:$B$9)=$H$1)*(MONTH($B$5:$B$9)=$J$1)*($C$5:$C$9=G6)*($A$5:$A$9))

**twiggywales** · 01-16-2013, 07:08 AM

try this

Please Login or Register  to view this content.

then drag

I'm not great with SUMIFS and I couldn't get it to work but this did.

if anyone can explain how to use SUMIFSfor this id be greatful

**JungleJme** · 01-16-2013, 07:10 AM

Originally Posted by Fotis1991

Why your expected result for year 2012, month=12 and "Me", is 80 ?? B9 = November of 2012. not December.

The report is a Year To Date report so the requirement is for the month to be less than or equal to the report month AND the Year to be the same year... will play with your solution though. Thanks!

**twiggywales** · 01-16-2013, 07:10 AM

fotis its because he needs if the month is less then the month in question (November before December so 20 + 80)

**Fotis1991** · 01-16-2013, 07:14 AM

Ok. I understand. so.

=SUMPRODUCT((YEAR($B$5:$B$9)=$H$1)*(MONTH($B$5:$B$9)<=$J$1)*($C$5:$C$9=G6)*($A$5:$A$9))

**JungleJme** · 01-16-2013, 07:15 AM

Thanks twiggy - only problem with that is if there are entries for 2011,2010 etc... (which there will be)...that formula will still sum the value...

**JungleJme** · 01-16-2013, 07:17 AM

Thanks Fotis - that seems to have cracked it

**twiggywales** · 01-16-2013, 07:20 AM

sorry that was my fault I didn't read that correctly the YEAR part. I'm glad you have it sorted though.

**Fotis1991** · 01-16-2013, 07:21 AM

You are welcome. Thanks for the reb*.

**JungleJme** · 01-16-2013, 07:21 AM

I do

thanks for the help! If it didn't feel like i am trying to think through a fog bank at the moment i may have got there eventually - although considering my unnatural fear of SUMPRODUCT maybe not!

SUMIFS and Date Criteria (Month Year etc...) but without helper column?

LinkBack

Thread Tools

Rate This Thread

Display

SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Re: SUMIFS and Date Criteria (Month Year etc...) but without helper column?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions