• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIFS based on a list

tarynmahon

tarynmahon

Member
Hi All,
I was wondering if there was a better way of doing this formula;
*Hint* (Its exactly the same formula 4 times)

=SUMIFS(APR14.DATA!$O:$O,APR14.DATA!$Z:$Z,$C11,APR14.DATA!$X:$X,'P&L CATS'!$A$1)+SUMIFS(APR14.DATA!$O:$O,APR14.DATA!$Z:$Z,$C11,APR14.DATA!$X:$X,'P&L CATS'!$A$2)+SUMIFS(APR14.DATA!$O:$O,APR14.DATA!$Z:$Z,$C11,APR14.DATA!$X:$X,'P&L CATS'!$A$3)+SUMIFS(APR14.DATA!$O:$O,APR14.DATA!$Z:$Z,$C11,APR14.DATA!$X:$X,'P&L CATS'!$A$4) Etc...

Basically the highlighted values depicts each line of a list of information, rather than adding the same formula over an over again like Im doing above and just changing this line I was wondering if theres a formula that could just lookup column A:A ?
Preferably without doing a SUMPRODUCT as this takes a lot of time with the size of the spreadsheet that Im using, also for this reason I dont think I should upload a sample.

Any help will be greatly appreciated
 
Sorry I don't think you understand, I don't need the formula to change, the foruma is just in one cell so no copying down, the only thing I want to change is one of the criteria but instead of it referencing one cell I need it to reference a column
 
Like this:

=SUMPRODUCT(SUMIFS(APR14.DATA!$O:$O,APR14.DATA!$Z:$Z,$C11,APR14.DATA!$X:$X,'P&L CATS'!$A$1:$A$4))
 
Why without SUMPRODUCT?

If you are worried about slow calculations, the formula I gave you is almost exactly as efficient as your original formula but written more concisely. If your original formula is fine in terms of calculation speed then this one will be fine too. This is because the number crunching in the formula is still done by the SUMIFS componenet - the SUMPRODUCT is just used to deference A1:A4 into an array and then sum the results from each SUMIFS() calculation.
 
Sure, no problem. You are right to be generally sceptical about the efficiency of SUMPRODUCT() formulas.

If you're interested in some benchmarking in different sumproduct variations on this style of conditional sum, have a look here.
 
You must log in or register to reply here.
Back
Top