MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF


Posted by Matthew Schmitt on May 08, 2001 8:24 AM

I need to make a countif statement that will count the number of cells in range b33:b41 that is greater than 3 but less than or equal to 10. I tried this, but it doesnt seem to want to work:

=COUNTIF(b33:b41,">3 and <=10")


Posted by Kevin James on May 08, 2001 8:56 AM

Array

Hi Matt:

After entering formula, press Ctrl-Shift-Enter
=SUM(($B$33:$B$41>3)*($B$33:$B$41<=10))

I tested it and, given your scenario, it works.

Kevin.

Posted by Matthew Schmitt on May 08, 2001 9:36 AM

Re: Array

THANKS! NOW IF I WANT TO DO A SUM OF THE AMOUNTS IN COLUMN D FOR THE LINES OF THE SHEET THAT FALL INTO THE COUNTIF CRITERIA, WHAT DO I DO?

Posted by Kevin James on May 08, 2001 9:43 AM

Re: Array

Matthew,

I know I am not understanding you because I'm sure you know how to write a SUM function.

Are you saying you want the same criteria (>3,<=10)?

Kevin

Posted by Matthew Schmitt on May 08, 2001 10:00 AM

Re: Array

Yes, but it has to sum the items from column D that are contained in the same line as the items in cloumn B that meet the count criteria

For example, if lines 33, 36, and 38 are the lines that get counted, and the dollar amounts in column d of those lines are 100, 300, and 500, then I want to sum those three dollar amounts.
Hope this clears it up a little for ya!

Thanks alot!

Posted by Aladin Akyurek on May 08, 2001 10:00 AM

Re: Array

Just a small extention to the array-formula you proposed:

=SUM(($B$33:$B$41>3)*($B$33:$B$41<=10)*($B$33:$B$41))

Aladin

Posted by Aladin Akyurek on May 08, 2001 10:05 AM

Re: Array

In that case, array-enter:

=SUM(($B$33:$B$41>3)*($B$33:$B$41<=10)*($D$33:$D$41))

I'd suggest that you put 3 an 10 in cells of their own, say 3 in A1 and 10 in A2:

=SUM(($B$33:$B$41>$A$1)*($B$33:$B$41<=$A$2)*($D$33:$D$41))

Aladin

Posted by Kevin James on May 08, 2001 10:38 AM

Aladin pulls his magic again

Thanks Aladin,

I got busy with real work. I can't believe how much I've learned on this site just since I started browsing here a couple weeks ago.

Kevin