Sum Product vs Sum(If)

[anandmr65]

Hi,

Can somebody tell me which of two is better & why.

Counting Based on Multiple Criteria
We can use =SUMPRODUCT((C2:C6<1000)*(B2:B6="red")) or we can also use
array formula with Sum(if((C2:C6<1000),if(B2:B6="red")),1,0) (syntax
might not be exact).

IS there any advantage of using Sumproduct over using Sum(if, or vice
versa.

Please educate, since I have been using the array formula version
(sum(if).

Thanks in advance.

Regards
Anand

 

[Bob Phillips]

I think the difference would be small if not negligible, as they both work
on arrays. I prefer SP, as it looks more intuitive to me when the formula is
written out.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Don Guillett]

I personally always use sumproduct if I can. Some say less resources and no
CSE

average(if
is a good array formula

 

[Biff]

Sum(if((C2:C6<1000),if(B2:B6="red")),1,0) (syntax might not be exact).

You've got some misplaced ( ).

That could be better expressed as:

=SUM((C2:C6<1000)*(B2:B6="red"))

I'm like Don, I only use array formulas when I have to but a lot depends on
the size and functionality of the particular file. And, as Bob pointed out,
the difference between the SUM(IF and the SUMPRODUCT is probably negligible.

Biff