SUMPRODUCT error #value! when a column contains text and number

[kitsa]

Hi,
I have an issue with my code and I can't work out how to fix. The formula works when there is no text in a column "E", but when blank or just numbers, it works. How can I get this to work when there is text in column "E".

=SUMPRODUCT(($A$3:$A$12=N3)*($C$2:$J$2=$P$2)*$C$3:$J$12)

 

[SanjayGMusafir]

Update your Current Excel Version & Platform in your profile and remember to Save it. This way it will be visible to anyone who reaches your thread to help.
Solution to your problem depends upon what version of Excel your are currently using...

 

[bebo021999]

Replace * with ",":

=SUMPRODUCT(($A$3:$A$12=N3),($C$2:$J$2=$P$2),$C$3:$J$12)

 

[kitsa]

Update your Current Excel Version & Platform in your profile and remember to Save it. This way it will be visible to anyone who reaches your thread to help.
Solution to your problem depends upon what version of Excel your are currently using...

Hi,
I'm using Office Professional Plus 2016, how do I update?

 

[kitsa]

Replace * with ",":

=SUMPRODUCT(($A$3:$A$12=N3),($C$2:$J$2=$P$2),$C$3:$J$12)

Hi,
I changed it , but it didn't work.

 

[bebo021999]

Or, again:

=SUMPRODUCT(($A$3:$A$12=N3)*($C$2:$J$2=$P$2),$C$3:$J$12)

 

[bosco_yip]

Using this one :

=SUMPRODUCT(($A$3:$A$12=N3)*($C$2:$J$2=$P$2),$C$3:$J$12)

or,

=SUMPRODUCT(($A$3:$A$12=N3)*($C$2:$J$2=$P$2)*N(IF({1},$C$3:$J$12)))

Regards

 

[bosco_yip]

'

 

[kitsa]

Or, again:

=SUMPRODUCT(($A$3:$A$12=N3)*($C$2:$J$2=$P$2),$C$3:$J$12)

Worked thanks

 

[kitsa]

Using this one :

=SUMPRODUCT(($A$3:$A$12=N3)*($C$2:$J$2=$P$2),$C$3:$J$12)

or,

=SUMPRODUCT(($A$3:$A$12=N3)*($C$2:$J$2=$P$2)*N(IF({1},$C$3:$J$12)))

Regards

This also worked,
Thanks