How can I use a SUM array formula that ignores letters?

Good Morning,

I have two array formula's, one to count hours, the other to count days. Both are based on: type of worker, day of week, and week number. The problem is if I use a "V" or "T" for vacation or training, then the array formula will not work. Currently I leave the field blank if they are not working, but it would be nice to see whether they are on vacation or training. Is there a way i can have my cake and eat it too?

Counting Days
{=SUM(($E$5:$MN$50>0)*($B$5:$B$50=$D56)*($E$3:$MN$3=F$54)*($E$2:$MN$2=$E$67))}

Counting Hours
{=SUM(($E$5:$MN$50)*($B$5:$B$50=$D56)*($E$3:$MN$3=F$54)*($E$2:$MN$2=$E$67))}

Here is a breakdown of what each of the ranges mean:

• $E$5:$MN$50 is the area which the hours are forecasted

• $B$5:$B$50 is the column for which type of worker (Electrician, Pipefitter, etc)

• $D56 is the specific type of worker we are counting for

• $E$3:$MN$3 is a row with all the week days ("Fri, Sat, Sun...)

• F$54 is the specific day we are counting for

• $E$2:$MN$2 is a row with all the week numbers

• $E$67 is the specific week number we are counting for

The values in E5:MS:50 are supposed to be 0-12, V, T, or X. If a letter is there i get an output of #Value! with a message "A value used in the formula is of the wrong data type"

@FDibbins: Unfortunately i cannot attach my workbook because our IT policy is restricting me.

@daddylonglegs: I was not aware that you could bypass CSE with SUMPRODUCT, thanks for the tip Unfortunately using the SUMPRODUCT formula does not work any better as the CSE with the SUM formula. The formula for counting days just counts how many cells are greater than 0. The array works great when only numbers are involved, but breaks down whenever letters or strings are involved. I tried nesting IF and ISNUMBER like this:

=SUMPRODUCT(IF(ISNUMBER($E$5:$MN$50),($E$5:$MN$50),0)*($B$5:$B$50=$D56)*($E$3:$MN$3=F$54)*($E$2:$MN$2=$E$67))

Unfortunately it did not work, all i got returned was zero regardless if i had any letters in the array or not. Anyone have any other ideas?

Hey guys, i got it figured out. Apparently when using the SUMPRODUCT Formula, you can use comma instead of *. If you use a comma, it ignores the text... Thanks for your responses but this thread is considered solved.

Here are the working formula's:

Counting Hours
=SUMPRODUCT(($E$5:$MN$50),($B$5:$B$50=$D56)*($E$3:$MN$3=AJ$54)*($E$2:$MN$2=$I$67))

Counting Days
=SUMPRODUCT(($E$5:$MN$50>0),($B$5:$B$50=$D56)*($E$3:$MN$3=AJ$54)*($E$2:$MN$2=$I$67))

@daddylonglegs: You sir are awesome. My applogies, and I have added rep as you deserve it! Please disregard my last post, I must have not copied it properly the first time. Thanks again!