SUMPRODUCT formula wont work!

[charliew]

Hi there,

I have been trying to sumproduct how many inquiries sales rep CS has sent through on each day... my formula,

=SUMPRODUCT($B$2:$B$1000="Ext")*($I$2:$I$1000="CS")*(INT($F$2:$F$1000)=AB63)

doesn't work and is only returning a "0" value,

I basically need it to search coloumn B for Ext, then search for CS, then search for the specific date then count how many Ext CS has sent through for the corresponding date! The integer is because the search array has date and time and the AB63 is just the date

any help greatly appreciated!!

Thank you

 

[steve the fish]

You are missing some brackets.

=SUMPRODUCT(($B$2:$B$1000="Ext")*($I$2:$I$1000="CS")*(INT($F$2:$F$1000)=AB63))

 

[charliew]

You are missing some brackets.

=SUMPRODUCT(($B$2:$B$1000="Ext")*($I$2:$I$1000="CS")*(INT($F$2:$F$1000)=AB63))

HAHA!! oh god! thank you...that's a bit annoying..and a little embarrassing!!


thanks again!!

 

[charliew]

Please help!!

Why doesn't this work:

=SUMPRODUCT((BM2:BM500="1")*($BF$2:$BF$10000="1")*($B$2:$B$10000="Ext")*($BS$2:$BS$10000=BU3))

it's giving me a #NA ! error, and i cant figure out why!!!

 

[FormR]

=SUMPRODUCT((BM2:BM500="1")*($BF$2:$BF$10000="1")*($B$2:$B$10000="Ext")*($BS$2:$BS$10000=BU3))

Hi, all of the ranges need to be of the same size. i.e. try changing that 500 to 10000.

Is there a reason why you are not using the more efficient COUNTIFS() formula. i.e.

=COUNTIFS(BM2:BM10000,"1",BF2:BF10000,"1",B2:B10000,"Ext",BS2:BS10000,BU3)

 

[charliew]

Hi, all of the ranges need to be of the same size. i.e. try changing that 500 to 10000.

Is there a reason why you are not using the more efficient COUNTIFS() formula. i.e.

=COUNTIFS(BM2:BM10000,"1",BF2:BF10000,"1",B2:B10000,"Ext",BS2:BS10000,BU3)

Changing the range didn't work using sumproduct because the countif kept coming up with an error saying i had entered too many arguments

 

[FormR]

the countif kept coming up with an error saying i had entered too many arguments

The one that I have suggested?

 

[charliew]

The one that I have suggested?

me being an idiot! i missed the S off....works PERFECTLY now THANKYOU!! have been going crazy for days trying to work this out!!!