Nested SUMIF statement or multiple SUMIF's

**Dan27** · 09-17-2009, 06:29 AM

Hello all

I was hoping someone would be able to help me. I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.

I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.

Thanks in advance

Dan

**DonkeyOte** · 09-17-2009, 06:32 AM

Which XL version are you using ?

What is the range to be summed ?

**Dan27** · 09-17-2009, 06:34 AM

I'm using Excel 2003. The range is actually 20k but I used 100 to simplify my question.

Thanks

**DonkeyOte** · 09-17-2009, 06:36 AM

You said you need to SUMIF - you have given us 2 conditional ranges for the criteria but not the range for summation... do you then mean a COUNTIF ?

=SUMPRODUCT(--(H1:H20000=10),--(J1:J100=907))

SUMPRODUCT used with large ranges will lead to poor performance... you will be best served adding a concatenation column at source, eg:

K1: =H1&"@"&J1
copied down

and then use a traditional COUNTIF

=COUNTIF(K1:K20000,"10@907")

which will be far more efficient.

**Dan27** · 09-17-2009, 06:40 AM

Sorry for not being clear DonkeyOte.

To be exact it would be =SUMIF(I1:I20000,"10" and J1:J20000,"907" then sum range K1:K20000

Thanks

**DonkeyOte** · 09-17-2009, 06:43 AM

OK so using SUMPRODUCT simply add the final range, eg:

=SUMPRODUCT(--(H1:H20000=10),--(J1:J20000=907),K1:K20000)

using concatenation (advised) where concatenated strings in L rather than K (given values to sum in K):

=SUMIF(L1:L20000,"10@907",K1:K20000)

this will be far more efficient (ie concatenation negates need for SUMPRODUCT/Array formulae)