SUMPRODUCT Array Formula Help

Hi All,

I'm having issues with the below formula. In row 2 I have Dates across B2 to JB2 (2012 calendar). In row 3 I have alphanumeric data (8 or 4, and H, F, S). I’m trying to sum the numbers that are less than or equal to today’s date. I can get the formula to work for one alpha but when i try all three i get #Value errors and can't figure out how to fix.

Please Login or Register  to view this content.

Any help is greatly appreciated.

Thanks,
Cullen

Try this:
{=SUMPRODUCT(--(B$2:JB$2<=TODAY()),(IF((B$3:JB$3)="H",0,(B$3:JB$3))))}

Using multiplication for coersion will result in an error if any part of the array being multiplied is text. In your case, you still have F and S in your array. Using the double unary for coersion will ignore the text.

***EDIT***
And if H=0, then we might as well ignore that as well and save use from making this an array formula

=SUMPRODUCT(--(B$2:JB$2<=TODAY()),B$3:JB$3)