We help IT Professionals succeed at work.

# sumifs for cumulative - more elegant way?

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

## View Solutions Only

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]))
``````

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

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