Sumproduct gives me error

Closed
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
- Jun 18, 2014 at 07:13 PM
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
- Jun 22, 2014 at 07:04 PM
Hi,

I am using the below formula, but it gives me error. Any ideas why is that? I really should work same as the countif formula but instead it gives me error...

=SUMPRODUCT((C4:C453="Juan")*(C4:C453="Alanzon")*(C4:C453="paul"))


I have used the below formula, which works, but would like to know how to use array formula to make it shorter.
=COUNTIF(C4:C453,"Juan")+COUNTIF(C4:C453,"Alanzon")+COUNTIF(C4:C453,"paul")

Much appreciated

3 replies

Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Jun 18, 2014 at 09:13 PM
jutulu, Good evening.

Try to use:
=SUMPRODUCT((C4:C453="Juan")+(C4:C453="Alanzon")+(C4:C453="paul"))

Is this what you desire?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
2
Jun 19, 2014 at 07:22 PM
Hi mazzaropi. Thanks, however, how can use array formula to make I shorter. Gracias
0
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
2
Jun 22, 2014 at 07:04 PM
Hi. Any ideas on the above as to how to use array formula to shorten the sumproduct formula? Thanks

Also, can sumproduct work with the * symbol too, or only with the + symbol?
Thanks
0