We help IT Professionals succeed at work.

sumifs for cumulative - more elegant way?

agwalsh
agwalsh asked
on
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
Comment
Watch Question

Top Expert 2015
Commented:
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...
Most Valuable Expert 2011
Top Expert 2011
Commented:
Why query the external file again? I'd use:

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

Commented:
Hahaha @ Rory...good one...didn't thought about that.. Ouch...
Rob HensonFinance Analyst
Commented:
not for points:

Rory - one too many $ locks

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

Thanks
Rob H
Most Valuable Expert 2011
Top Expert 2011

Commented:
Well spotted - thanks, Rob.

Author

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...