Help with SUMPRODUCT COUNTIF

[llb87]

Hi,


I have to work out the amount of values e.g. between 60 & 90, per sales manger.

I have a colum with numbers ranging from 1-1000 and a colum with a corresponding sales manager for each entry.


So i need to know how many entrys John Smith had between 60 & 90!


I can get the toatal amount of entries between 60 and 90 using this =SUMPRODUCT(--(H2:H3363>60),--(H2:H3363<90))


But when I try and add another condition in, to count how many of those are for each sales manger it doesn't work.


I have been trying this =SUMPRODUCT(--(H2:H3363>60),--(H2:H3363<90),--(F2:F3362="John Smith"))


Thanks Laura

 

[Faseeh]

Hi llb87,


Welcome to the fourms!


If you restate your thrid forula like this, it will give your Manager-wise sum between 60 & 90:


=SUMPRODUCT((H2:H3363>60)*(H2:H3363<90)*(F2:F3362="John Smith"))


Hope that helps,


Faseeh

 

[Marcilio]

Hi Laura, Good Morning.


I believe that the question error is located at a syntax problem.

You must use the same range on all clauses when using SUMPRODUCT function.


=SUMPRODUCT(--(H2:H3363>60),--(H2:H3363<90),--(F2:F3362="John Smith"))

H2:H3363

H2:H3363

F2:F3362 --> 62 final INSTEAD 63 final ???????


You can use both markers:

a) -- with a comma as separator

b) * wihtout separators


Tell us if it worked for you.


I hope it helps you.


Have a nice day.


Marcílio Lobão

---------------------------

Belo Horizonte, MG • Brazil

 

[llb87]

Thanks very much guys!!


It was a syntax problem!! Now I know how to deal with it in the future!!


Thanks,

Laura