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