Count instances of a value within a date-range

**Nate Westcott** · 01-06-2012, 02:41 PM

I'm trying to compose a formula which will count number of instances of a value within a given date range. My first attempt embarrassingly wrong:

=SUMIF((Sheet1!G2:G40000,"Orange")*(Sheet1!A2:A40000>=B500)*(Sheet1!A2:A40000<=B501))

Can anyone help me see how best to compose a formula that counts the number of times "Orange" appears in column G if the date in column A is between the parameters set in cells 500 and 501? I'm using "10/1/2011" in cell 500 and "12/30/2011" in cell 501.

Many thanks, in advance, to anyone who can assist!

**DonkeyOte** · 01-06-2012, 03:00 PM

If you're using XL2010 as implied you can use COUNTIFS

=COUNTIFS(Sheet1!G2:G40000;"Orange";Sheet1!A2:A4000;">="&B500;Sheet1!A2:A4000;"<="&B501)

modify delimiter per your own regional requirements (ie ; to ,)

**dilipandey** · 01-06-2012, 03:33 PM

Hi Nate,

See the attached file and let me know if this solves your purpose.

Regards,
DILIPandey

<click on below 'star' if this helps>

**DonkeyOte** · 01-06-2012, 03:35 PM

dilipandey, either wrong thread or wrong attachment.

**dilipandey** · 01-06-2012, 03:44 PM

You are right DonkeyOte..!! Thanks for the catch.

Hi Nate, See the correct file uploaded here.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Nate Westcott** · 01-06-2012, 04:28 PM

DonkeyOte: Thank you! I did have to modify the semi-colon to a comma, but I'm still receiving an error. Would I have to modify the command to "SEARCH", if I wanted to find text that appears among other text in the cell?

I think I'm having difficulty because one of the values ends with a quotation mark, like Orange "Yum-Soda". So I want to use "Orange" as the value to count, to simplify the formula, and to avoid problems with the quotation marks.

dilipandey: Much appreciated! When I plug that formula into my sheet, I'm receiving a "0" for the count. I think it has to do with the above explained issue.

**DonkeyOte** · 01-06-2012, 05:34 PM

COUNTIFS accepts Wildcards.

=COUNTIFS(Sheet1!G2:G40000,"*Orange*",Sheet1!A2:A40000,">="&B500,Sheet1!A2:A40000,"<="&B501)

earlier error because I used 40000 and 4000 and in COUNTIFS each dimension has to be identical in size.

@Dilipandey:

{=SUM(--($A$1:$A$17>=$B$3)*($A$1:$A$17<=$B$4)*($G$1:$G$17="Orange"))}

There is no need for the double unary operator in the above. The multiplication of the arrays will coerce the boolean array by default.

**dilipandey** · 01-07-2012, 03:36 AM

Hi Nate,

I believe you have missed the { sign in the formula which can be included if you enter the formula with Ctrl+Shift+Enter key combination.
Let me know if this helps.

@ DonkeyOte, I have checked the formula and it is working fine, not able to get you. Please explain a little further. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**DonkeyOte** · 01-07-2012, 06:10 AM

Originally Posted by dilipandey

@ DonkeyOte, I have checked the formula and it is working fine, not able to get you. Please explain a little further. Thanks.

I did not say it would not work merely that the double unary was superfluous:

=SUM(--({FALSE,TRUE})*({TRUE,TRUE}))

and

=SUM(({FALSE,TRUE})*({TRUE,TRUE}))

will both generate the exact same result, i.e 1

Multiplication will by default coerce both elements; the additional double unary operation is therefore an unnecessary overhead (albeit virtually non-existent)