# SUMIFS based on a list

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

#### sgmpatnaik

##### Active Member
@tarynmahon

Change this \$A\$1 to \$A1

copy and drag down

Thanks

#### tarynmahon

##### Member
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

#### Colin Legg

##### Active Member
Like this:

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

#### tarynmahon

##### Member
Thank you but I'm looking for a way of doing it without using SUMPRODUCT

#### Colin Legg

##### Active Member
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.

#### tarynmahon

##### Member
Oh ok, thank you, I'll give it a go, thanks very much

#### Colin Legg

##### Active Member
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.