Can I ignore text using SUMPRODUCT?

[tinderbox22]

Hello,

I'm attempting to do a multi-criteria sum lookup using SUMPRODUCT. I have three conditions and a data set. Within the data set, there are cells that contain text, thus causing a #VALUE error in my SUMPRODUCT formula. The formula works great if I remove the text. Is there a way to make the formula ignore text? I've tried a few things (using --, using ISNUMBER, using both), but none have worked yet, and I'm likely just not doing it correctly.

Formula looks like this:
=SUMPRODUCT(($D$6:$BC$6="January")*($B$19:$B$30="Atlanta")*($C$19:$C$30="TV")*($D$19:$BC$30))

Any help would be appreciated! The -- option still gave me a #VALUE error even when I changed the "*" multiplier to a ","

Thank you!

 

[Fluff]

How about
=SUMPRODUCT(($D$6:$BC$6="January")*($B$19:$B$30="Atlanta")*($C$19:$C$30="TV")*(ISNUMBER($D$19:$BC$30)))

 

[tinderbox22]

When I do that formula, it seems to be counting and not summing. I'm getting a result of "5" when I should be getting "415." There are five cells that qualify for that sum. I'm guessing that the ISNUMBER has turned all my numbers into 1's and 0's, and now it's multiplying 1x1x1x1 (three different TRUE criteria plus one TRUE cell with data) five times.

 

[Fluff]

You've understood what the formula is doing correctly
If you want to sum the numbers, then I know of no way of doing it, probably because I'm not that good with formulae.
Hopefully somebody else will come along with a suggestion

 

[Eric W]

You could try:

=SUMPRODUCT(($D$6:$BC$6="January")*($B$19:$B$30="Atlanta")*($C$19:$C$30="TV")*IFERROR($D$19:$BC$30+0,0))

you'll need to confirm the formula with Control+Shift+Enter.

 

[tinderbox22]

You could try:

=SUMPRODUCT(($D$6:$BC$6="January")*($B$19:$B$30="Atlanta")*($C$19:$C$30="TV")*IFERROR($D$19:$BC$30+0,0))

you'll need to confirm the formula with Control+Shift+Enter.

That worked! Thank you. So, you're creating a #VALUE error throughout the array but turning each one into a zero. I like it.

Many thanks to all suggestions!

 

[Domenic]

Or, you can use the comma syntax...

=SUMPRODUCT(($D$6:$BC$6="January")*($B$19:$B$30="Atlanta")*($C$19:$C$30="TV"),$D$19:$BC$30)

Now SUMPRODUCT has two arguments, each being the same size (12 rows by 15 columns), which is what the comma syntax requires.

Hope this helps!

 

[tinderbox22]

Or, you can use the comma syntax...

=SUMPRODUCT(($D$6:$BC$6="January")*($B$19:$B$30="Atlanta")*($C$19:$C$30="TV"),$D$19:$BC$30)

Now SUMPRODUCT has two arguments, each being the same size (12 rows by 15 columns), which is what the comma syntax requires.

Hope this helps!

This works as well. Thank you.

Question - why does the comma allow this to ignore text values?

 

[Domenic]

Quite simply, text values are ignored when the comma syntax is used with SUMPRODUCT. It's how the function is designed. And, as I've already mentioned, the ranges need to be the same size. Otherwise, #VALUE! will be returned.

 

[tinderbox22]

Perfect. Thanks again!