Unable to get Array Formula Using Sum Function to work

I am not able to get the sum function to pick off isolated data from the worksheet. I selects information from only one column instead of reading from two columns and combine the information. I will paste the formula that brought me the closest to the desired result.

=SUM((B98:B106="Jan"),(C98:C106="North")*D98:D106)

The result I get is 450 which is from three North results in the worksheet. My request is for the formula to return a combination of Jan & North Sales. The result is for three North without regard for Jan. Can anyone help on this?

Month Region Sales
Jan North 100
Jan South 200
Jan West 300
Feb North 150
Feb South 250
Feb West 350
Mar North 200
Mar South 300
Mar West 400

Try this formula (not an array formula)

Please Login or Register  to view this content.

It returns a zero value. It should return 100. Any other suggestions

A1:A4="Jan"

results in an array of this type {TRUE;FALSE;FALSE;FALSE}

To use this for calculation it has to be transformed to an array of numerical values this is archived with -- (double negative), minus and minus equals pluss. This could also be archived with multiplying the array by 1.

The result is then:

{1;0;0;0}

Select your formula and use Tools -> Formula auditing -> Evaluate formula. Then you can se how it work step by step.

Did you get my SUMPRODUCT formula to work as it should now ?