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


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile: http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=553920

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)

"anandmr65" wrote
in message ...

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


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile:
http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=553920

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

average(if
is a good array formula

--
Don Guillett
SalesAid Software

"anandmr65" wrote
in message ...

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


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile:
http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=553920

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

"anandmr65" wrote
in message ...

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


--
anandmr65
------------------------------------------------------------------------
anandmr65's Profile:
http://www.excelforum.com/member.php...o&userid=30728
View this thread: http://www.excelforum.com/showthread...hreadid=553920