Sumproduct help - CLOSED Thank You

[artizan]

My table show's the date in the first column and a name of a product in the second column together with a description ie:

(Col 1) - 10/09/08
(Col 2) - Bluebird BIP-5000 Corporate PDA and Desk Cradle Kit with Laser, 2MP Camera, Bluetooth, WLAN 802.11, GSM/GPRS, IrDA with USB Cradle and PSU

I am currently using the formula:

=SUMPRODUCT(--(MONTH($A$2:$A$1000)=9,--($B$2:$B$1000 = "Bluebird"))

The result shows 0

Can someone explain how I can get the function to read the whole of the description at pick out the product name "Bluebird"

Grateful for any help in solving this issue

Artizan

[Richard Schollar]

If you to check that the second column starts with "Bluebird" then you can use the following:

=SUMPRODUCT(--(MONTH($A$2:$A$1000)=9),--($A$2:$A$1000<>""),--(LEFT($B$2:$B$1000,8) = "Bluebird"))

Note I have also included the --($A$2:$A$1000<>"") argument as otherwise blank date cells will return 1 when MONTH() is applied to them, which may throw your formula result out.

[artizan]

Unfortunately, This seems to give the same result. I have checked the Syntax and that seems to be ok. Grateful for any help.

[NBVC]

How about:

=SUMPRODUCT(--(MONTH($A$2:$A$1000)=9),--($A$2:$A$1000<>""),--(ISNUMBER(SEARCH("Bluebird",$B$2:$B$1000))))

[artizan]

Thank you RichardSchollar & NBVC this has now worked! Very Gratful for your help.