SUMPRODUCT with COUNTIF issue

[Buck4145]

Hi,

I'm new to this forum and have an issue with a SUMPRODUCT formula with a COUNTIF clustered within. Basically I'm trying to search one column for a specific value and return how many lines fall within a range from a second column. Specifically I'm trying to find in G:G all values of R01 that in E:E fall within the range of 183 - 365 (6 - 12 months) for a seperate Stats sheet. I have used a select range rather than whole columns as this was giving me issues, this is what I have so far:

=SUMPRODUCT(('Current Trainees'!G8:G500="R01")*(COUNTIF('Current Trainees'!E8:E500,">=184")-(COUNTIF('Current Trainees'!E8:E500,"<=365"))))

I am finally getting a value returned to me rather than an error however the value is incorrect.

Thanks in advance

 

[jim may]

Try

=SUMPRODUCT(('Current Trainees'!G8:G500="R01")*(COUNTIF('Current Trainees'!E8:E500,">=184"))*(COUNTIF('Current Trainees'!E8:E500,"<=365")))

 

[Buck4145]

Unfortunately not it, that gave me worse. It seems to be returning how many R01's there are without discounting the ones that dont fall within the additional range requirements and then multiplying by the reulting Countif total.

Days since Joining</SPAN>	Sign up Date</SPAN>	Level</SPAN>
518</SPAN>	11-Apr-13</SPAN>	R00</SPAN>
98</SPAN>	05-Jun-14</SPAN>	R00</SPAN>
757</SPAN>	15-Aug-12</SPAN>	R01</SPAN>
154</SPAN>	10-Apr-14</SPAN>	R00</SPAN>
779</SPAN>	24-Jul-12</SPAN>	R02</SPAN>
381</SPAN>	26-Aug-13</SPAN>	R00</SPAN>
562</SPAN>	26-Feb-13</SPAN>	R02</SPAN>
766</SPAN>	06-Aug-12</SPAN>	R01</SPAN>
287</SPAN>	28-Nov-13</SPAN>	R00</SPAN>
15</SPAN>	27-Aug-14</SPAN>	R00</SPAN>
755</SPAN>	17-Aug-12</SPAN>	R02</SPAN>
113</SPAN>	21-May-14</SPAN>	R00</SPAN>
220</SPAN>	03-Feb-14</SPAN>	R00</SPAN>
108</SPAN>	26-May-14</SPAN>	R00</SPAN>
1148</SPAN>	21-Jul-11</SPAN>	R01</SPAN>	60</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>

 

[jim may]

OK, Try this...

=SUMPRODUCT(('Current Trainees'!G8:G500="R01")*('Current Trainees'!E8:E500>=184)*('Current Trainees'!E8:E500<=365))

 

[Buck4145]

That is perfect, Thankyou very much. a little less bulky than setting up a key.

Buck