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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try

=SUMPRODUCT(('Current Trainees'!G8:G500="R01")*(COUNTIF('Current Trainees'!E8:E500,">=184"))*(COUNTIF('Current Trainees'!E8:E500,"<=365")))
 
Upvote 0
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>
 
Upvote 0
OK, Try this...

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

Forum statistics

Threads
1,218,811
Messages
6,144,610
Members
450,559
Latest member
kwenda farai

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
Back
Top