SUMPRODUCT with COUNTIF issue

Buck4145

New Member
Joined
Sep 10, 2014
Messages
13
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
Try

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

Buck4145

New Member
Joined
Sep 10, 2014
Messages
13
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
OK, Try this...

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

Buck4145

New Member
Joined
Sep 10, 2014
Messages
13
That is perfect, Thankyou very much. a little less bulky than setting up a key.

Buck
 

Watch MrExcel Video

Forum statistics

Threads
1,127,798
Messages
5,626,951
Members
416,211
Latest member
lanka123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top