sumifs for cumulative - more elegant way?

hi Folks
I'm working on a way to generate year to date numbers and I've come up with a way using sumifs but it seems very long winded to me and I was wondering if there was a less clunky way to do it than the way I am doing...I don't want to go into a VBA solution to be honest...I'm just curious about a more streamlined way to do it...the one I'm curious about is in the Cumulative Spend sheet...
EE-sumifs-procurement-mtd-ytd-spend.xlsx
LVL 1
agwalshAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Why query the external file again? I'd use:

=SUMIF($B$2:$D$2,"<>*YTD",$B3:D3)
0
 
Saurabh Singh TeotiaCommented:
You can use this formula...

=sumproduct(('G:\Excel Exercises\SumIfS- Procurement\Procurement_sumifs-source-data-completed.xlsm'!Table1[Cost Centre]=$A3)*
(('G:\Excel Exercises\SumIfS- Procurement\Procurement_sumifs-source-data-completed.xlsm'!Table1[Month]=B$2)
+('G:\Excel Exercises\SumIfS- Procurement\Procurement_sumifs-source-data-completed.xlsm'!Table1[Month]=D$2))*
('G:\Excel Exercises\SumIfS- Procurement\Procurement_sumifs-source-data-completed.xlsm'!Table1[Cost]))

Open in new window


In sumproduct + sign means or and it will do the same as sumifs

Saurabh...
0
 
Saurabh Singh TeotiaCommented:
Hahaha @ Rory...good one...didn't thought about that.. Ouch...
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Rob HensonFinance AnalystCommented:
not for points:

Rory - one too many $ locks

=SUMIF($B$2:D$2,"<>*YTD",$B3:D3)

Thanks
Rob H
0
 
Rory ArchibaldCommented:
Well spotted - thanks, Rob.
0
 
agwalshAuthor Commented:
Sigh, you people...just f***** amazing..went with =SUMIF($B$2:E$2,"<>*YTD",$B3:D3) . Had to tweak it a tiny tiny bit. Thank you all so much..I KNEW there was a more elegant solution than what I had out there.. and of course you proved me right...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.