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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

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,633
Messages
5,626,009
Members
416,152
Latest member
TedX

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