CountIf with And

Novice Excel user on Excel 2003.  I have a yearly data input sheet to track 
codes.  I use the date (fomatted as 1/01/2009) and a code.  I want to track 
the codes in a mothly chart for ease in summarization and improvment 
tracking.  The codes are used to define a department and an error in that 
department  (code 1a means - AR department and error a).  I need to pull and 
total the amount for each code for each month.  After searching for a while 
it seems that using CountIf with And does not work.  Any suggestions would be 
greatly appreciated.
-- 
Mike
0
Mike1154 (1216)
4/27/2009 4:18:07 PM
excel 39879 articles. 2 followers. Follow

12 Replies
810 Views

Similar Articles

[PageSpeed] 30

=SUMPRODUCT(--(MONTH(date_range)=4),--(code_range=code_value),amount_range)

-- 
__________________________________
HTH

Bob

"Mike" <Mike@discussions.microsoft.com> wrote in message 
news:C8DBEB88-9FD8-4BBF-B319-72F088CA4A45@microsoft.com...
> Novice Excel user on Excel 2003.  I have a yearly data input sheet to 
> track
> codes.  I use the date (fomatted as 1/01/2009) and a code.  I want to 
> track
> the codes in a mothly chart for ease in summarization and improvment
> tracking.  The codes are used to define a department and an error in that
> department  (code 1a means - AR department and error a).  I need to pull 
> and
> total the amount for each code for each month.  After searching for a 
> while
> it seems that using CountIf with And does not work.  Any suggestions would 
> be
> greatly appreciated.
> -- 
> Mike 


0
BobNGs (423)
4/27/2009 4:44:23 PM
Hello Mike,

You might want to start with SUMPRODUCT and end up with my UDF Pfreq:
http://www.sulprobil.com/html/sumproduct.html

Regards,
Bernd
0
bplumhoff1 (208)
4/27/2009 4:55:14 PM
Thanks Bob.  The formula I used was =SUMPRODUCT(--(MONTH('Data 
Entry'!B2:B65536)=4),--('Data Entry'!C2:C65536="1a"),'Data Entry'!J2:J15377)

I'm not sure what you meant by amount range.  I added column J to give a 
value of 1 for every entry and used that for the amount range.  My dates are 
in B and my codes are in C.  The result I got was #VALUE!.  I'm sure this is 
a result of my inexperience but not sure what I have done wrong.  Can you 
tell from my formula?
-- 
Mike


"Bob Phillips" wrote:

> =SUMPRODUCT(--(MONTH(date_range)=4),--(code_range=code_value),amount_range)
> 
> -- 
> __________________________________
> HTH
> 
> Bob
> 
> "Mike" <Mike@discussions.microsoft.com> wrote in message 
> news:C8DBEB88-9FD8-4BBF-B319-72F088CA4A45@microsoft.com...
> > Novice Excel user on Excel 2003.  I have a yearly data input sheet to 
> > track
> > codes.  I use the date (fomatted as 1/01/2009) and a code.  I want to 
> > track
> > the codes in a mothly chart for ease in summarization and improvment
> > tracking.  The codes are used to define a department and an error in that
> > department  (code 1a means - AR department and error a).  I need to pull 
> > and
> > total the amount for each code for each month.  After searching for a 
> > while
> > it seems that using CountIf with And does not work.  Any suggestions would 
> > be
> > greatly appreciated.
> > -- 
> > Mike 
> 
> 
> 
0
Mike1154 (1216)
4/27/2009 5:31:02 PM
Hello Mike,

Bob gave you a generic answer.

If its just counting you want and if your dates are in col B and your
codes are in col C then
=SUMPRODUCT(--(MONTH(B1:B365)=4),--(C1:C365="1A"))
for example, might give you what you need. Please note that the length
of your ranges needs to be identical.

But: If you select an area of let's say 30 rows and exactly 2 columns
and you array-enter
=Pfreq(C1:C365,MONTH(B1:B365))
you get your statistic for all appearing codes and all months
automatically.

This might save some manual effort and operational risk in case codes
are changing...

Regards,
Bernd
0
bplumhoff1 (208)
4/27/2009 7:35:10 PM
Thanks so much Bernd.  One small issue though.  My formula looks like this - 
=SUMPRODUCT(--(MONTH('Data Entry'!$B$2:$B$365)=3),--('Data 
Entry'!$C$2:$C$365="1A")).  It does work, but is only tracking down to B and 
C 365.  I need this to track all the way to the bottom of the sheet if 
needed.  When I change the range to allow this it gives me #VALUE! as the 
result.  I think I need to use this style formula as the next row down will 
be checking for error code 1B.  Multiple errors can be recorded each day for 
one code.
-- 
Mike


"Bernd P" wrote:

> Hello Mike,
> 
> Bob gave you a generic answer.
> 
> If its just counting you want and if your dates are in col B and your
> codes are in col C then
> =SUMPRODUCT(--(MONTH(B1:B365)=4),--(C1:C365="1A"))
> for example, might give you what you need. Please note that the length
> of your ranges needs to be identical.
> 
> But: If you select an area of let's say 30 rows and exactly 2 columns
> and you array-enter
> =Pfreq(C1:C365,MONTH(B1:B365))
> you get your statistic for all appearing codes and all months
> automatically.
> 
> This might save some manual effort and operational risk in case codes
> are changing...
> 
> Regards,
> Bernd
> 
0
Mike1154 (1216)
4/27/2009 8:01:02 PM
Hello Mike,

Increase the index 365 until you get an error. Do you have error
values in col B or C?

Why don't you press ALT + F11, insert a new module and copy my macro
code for Pfreq and try that?

SUMPRODUCT is highly overrated, I think:
http://www.sulprobil.com/html/sumproduct.html

Regards,
Bernd
0
bplumhoff1 (208)
4/27/2009 8:37:48 PM
Again, thank you Bernd.  I am VERY inexperienced with VBA and don't know how 
to do anything.  I opened the new module but had no idea what to do with it.  
The error ocurrs in both B & C when I increase them or decrease from 365 
individually but I tried them together and it worked fine.  Can you recommend 
a good website for beginners like me to learn some about VBA?  Thank you for 
your patience and assistance.
-- 
Mike


"Bernd P" wrote:

> Hello Mike,
> 
> Increase the index 365 until you get an error. Do you have error
> values in col B or C?
> 
> Why don't you press ALT + F11, insert a new module and copy my macro
> code for Pfreq and try that?
> 
> SUMPRODUCT is highly overrated, I think:
> http://www.sulprobil.com/html/sumproduct.html
> 
> Regards,
> Bernd
> 
0
Mike1154 (1216)
4/27/2009 9:06:02 PM
"Bernd P" <bplumhoff@gmail.com> wrote in message 
news:833cb72c-2fbc-446e-be02-2aa0ccd9f698@v1g2000prd.googlegroups.com...
>
> SUMPRODUCT is highly overrated, I think:
> http://www.sulprobil.com/html/sumproduct.html


Maybe, but your thesis is poorly argued. You say

But there are limits to its usage: If you need to count all different 
strings in a column and to list them together with their number of 
occurrences, for example, you have to manually write down all different 
entries and you have to maintain this list manually.

What is wrong with a simple extracted list using formulae? You use a highly 
biased example to push your view without any mitigation. 


0
BobNGs (423)
4/28/2009 9:39:33 AM
Hello Bob,

Right, I pushed my view without mentioning formulae to extract unique
values because SUMPRODUCT is mostly being offered without doing so
(did you do so here?).

But you have a point here. I am thinking about showing four approaches
now:
1. Fomula extraction and SUMPRODUCT for *very* small lists.
2. My UDF's Pfreq and Sfreq for a wider range of lists (if a user is
able and willing to use VBA).
3. Pivot tables for more complex lists/statistics.
4. Database approach for problems of greater range / complexity.

I would be glad if you could turn your "Maybe" into an explicite
opinion: yes, no, or perhaps (when?)...

Regards,
Bernd
0
bplumhoff1 (208)
4/28/2009 5:34:40 PM
"Bernd P" <bplumhoff@gmail.com> wrote in message 
news:af004199-a637-4711-adae-d5c069dd2415@r31g2000prh.googlegroups.com...
> Hello Bob,
>
> Right, I pushed my view without mentioning formulae to extract unique
> values because SUMPRODUCT is mostly being offered without doing so
> (did you do so here?).

Of course I did, because the OP asked for the solution and gave no mention 
to the fact that he needed to extract the unique values to tabulate the 
results. If he had I would have covered that aspect as well, but as he 
didn't ...

> But you have a point here. I am thinking about showing four approaches
> now:
> 1. Fomula extraction and SUMPRODUCT for *very* small lists.


My answer to that is that it depends. Lots of SPs on large tables will be 
slow, and it is better that users are aware of this. However, the VAST 
majority of users I would contend use SP in very limited scenarios, and it 
is not an issue. They not to solve a problem and SP Most often does this 
very necessarily.


> 2. My UDF's Pfreq and Sfreq for a wider range of lists (if a user is
> able and willing to use VBA).


Exactly, if they are able and willing. Many organisations preclude the use 
of same, many users are scared by it - goodness knows why, but that is life.


> 3. Pivot tables for more complex lists/statistics.


I agree, but often the source data needs priming for pivots. Pivots are very 
good, they are not great.


> 4. Database approach for problems of greater range / complexity.


Again I agree, especially when combined with pivots, but I am  professional 
developer. This would scare the bejeebers out of most of the posters here 
who are offered SP solutions.


> I would be glad if you could turn your "Maybe" into an explicite
> opinion: yes, no, or perhaps (when?)...


No, it is a maybe and always will be in my view.

SP is extraordinarily versatile (actually, it is nothing to do with SP, it 
is the ability to construct multiple condition tests, they could all be just 
as easily deployed in array entered formulae), and provides an excellent 
solution for 99.99% of the times in which they are offered (obviously that 
is my conjecture, not a fact).

But, anyone who is going to use any array formula, and SP is just an array 
formula it is just not array entered, should be aware of the difficulties 
that can arise, and they should be aware of the potential solutions (helper 
columns, VBA, etc.).

I would hope that anyone who does get an SP solution and then encounters 
problems would either do some research into the issue, or ask some more and 
learn the tings that we know implicitly (isn't it amazing how much hard work 
and experience goes into implicit knowledge <g>), and develop their skills 
accordingly. But I would be willing to bet these would be few and far 
between.

I know array formulae can give rise to problems when used in the wrong 
situations, but what can't (hundreds of pivots all with separate caches, 
badly designed databases, linked workbooks, shared workbooks, ... I could go 
on).

I think it is disingenuous to make the sort of sweeping statements  that you 
did. In fact, on writing this, I have changed my view, it is not a maybe, it 
is a definite no. SUMPRODUCT, or in actuality the usage in the way that 
SUMPRODUCT has evolved, is a very useful function.

To finish, I also disagree strongly with a statement you made in your SP 
Pros. You said, ... You do not need Excel's � worksheet functions COUNTIF or 
SUMIF. Actually, you should not (never!) even use them because you might 
need to increase the number of conditions - and they only support ONE 
(condition) ... This is very bad advice in my view, users should be aware of 
as many tools at their disposal as possible, and use the correct one (I 
prefer to use a screwdriver with screws rather than a hammer). Failure to do 
so inevitable leads to abuse of the few tools that they do know, giving rise 
to exactly the sort of problems that you hang on SUMPRDUCT.



0
BobNGs (423)
4/30/2009 8:55:05 AM
Hello Bob,

Thanks for your frank and detailed answer.

We do not agree in some points.

My conclusion is that I might need to provide some more detailed
examples to convince more users of SUMPRODUCT's limits and of its time-
wasting tendency.

Regards
Bernd

0
bplumhoff1 (208)
5/2/2009 7:32:25 AM
Hello Mike,

At my site I started with a rudimentary intro:
http://www.sulprobil.com/html/vba_intro.html

Some more links are offered at my first (general comments page) there.

Regards,
Bernd
0
bplumhoff1 (208)
5/2/2009 9:34:08 AM
Reply:

Similar Artilces:

CountIf Statement
Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Criteria/CountIf and Pivot Table
Hello: My data looks like this ID Date CWA Amount 1 08/12/2006 0 $0.00 2 08/13/2006 1 $10.00 3 08/14/2006 1 $20.00 4 08/01/2006 0 $0.00 Based on the above data, I want to create a pivot, by month (I know I only have Aug here) to show the following: Sum of CWA Count of CWA Percent of cases that were submitted with CWA. The CWA is an indicator field that looks at another field (not listed here) to determine if there is cash in a...

Countif
I am trying to solve the following problem i) cells A1 to A10 have either M or F (male or female) ii) cells B1 to B10 have either (grades) A B or C iii) I want (eg cell B13) to state the number of female students who scored A... and cell B14; how many female students who gained B etc I have tried various things including countif, sumif etc. It seems like a straight forward problem but the solution evades me! Eddie =SUMPRODUCT((A1:A10="F")*(B1:B10=LEFT(ADDRESS(ROW(B1),ROW(A1),4),1))) in Cell B13 and filled down to Cell B15. Alan Beban Eddie wrote: > I am trying to solve ...

CountIf with And
Novice Excel user on Excel 2003. I have a yearly data input sheet to track codes. I use the date (fomatted as 1/01/2009) and a code. I want to track the codes in a mothly chart for ease in summarization and improvment tracking. The codes are used to define a department and an error in that department (code 1a means - AR department and error a). I need to pull and total the amount for each code for each month. After searching for a while it seems that using CountIf with And does not work. Any suggestions would be greatly appreciated. -- Mike =SUMPRODUCT(--(MONTH(date_range)=4),...

Excel countif and
is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1$3371a4f0$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

Countif() formula
Hi I have a column in Excel, D7:D200, filled of real numbers. Also the cell F3 includes a number (variable cell). I would like to count how many numbers of the column D7:D200 are >F3, but in successive ranges of D column. (That means how many numbers in: D7:D8 > F3, D7:D8 > F3, D7:D9 > F3…………….and finally D7:D200 > F3). To do that I have applied in E7 the formula: Countif(D7:$D$7 ; “>F3”) and expand down to E200. Unfortunately the above formula returns zero in all applied cells. If I change the F3 in the formula using a stable number (2,3,4 etc) the formula works, but...

CountIF by Background Color
Need a formula to count the cells in a range with a background color yellow (6). You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you...

Countif Formula with changing criteria
Hi. I'm trying to create a formula that will count how many numbers in a list of ten that are greater than a changing number in a cell. The cell changes as it has a formula attached to it. I used as the criteria >b1, but for some reason this doesn't work. As this number changes it has to be greater than whatever the cell value is at that time. -- Thanks! Stephen try =sumproduct(--(range>B1),1) "Stephen" wrote: > Hi. I'm trying to create a formula that will count how many numbers in a list > of ten that are greater than a changing number in a cell. T...

countif
I am trying to count a range of numbers in a column I created in excel to see how many times a particlar range appears. For example, I have a column with 10 rows that has a unique zip codes in each of the cells. 90001 90003 91005 90009 90207 94001 96007 97009 98009 98991 I want to know how many times each number below 94000 occurs and how many instances in which numbers greater than 94000 occur. What formula do I use. Thank you. cp Data in A1 to A10. Try this: =COUNTIF(A1:A10,">94000")&" above 94000 and "&COUNTIF(A1:A10,"<94000")&&quo...

IF COUNTIF
I am trying to get this to work =IF(COUNTIF(D83:S83,"=X"),"N","Y"),+IF(COUNTIF(L83,"=X"),"N","P") so that if L83 is selected the result in the cell would be P. Perhaps I am using the wrong function. Ideas anyone? Thank you. -- Mary Looks like the wrong function. When you say selected, what do you mean? It looks like you might just want =IF(L83="X","P","N") HTH Bob "Hmmmmm?" <Hmmmmm@discussions.microsoft.com> wrote in message news:DE1EDCE6-5F48-455A-B50E-185...

countif blanks
I need to count the number of blanks in a range. I'm trying countif(H1:H20,"<>""") but it's not working. Any ideas? =COUNTIF(H1:H20,"<>"&"") Or to account for the "dreaded SPACE BAR" =SUMPRODUCT((LEN(TRIM(H1:H20))>0)*1) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Radon" <Radon@discussions.microsoft.com> wrote in message news:0924636B-562F-4530-B972-FB7964F5F58C@microsoft.com... >I need to count the number of blanks in a range. I'm trying > countif(H1:...

Need help with CountIF function
Hello, Please help. I have a columne A with department codes A, B, C, ... the department codes are multiple lines, column B with numbers when there are activity in that department. Can I do a countif function or other function to summerize how many activity each department? To count how many times there is activity for dept A: =SUMPRODUCT(--(A2:A100="A"),--(ISNUMBER(B2:B100)) To sum the numbers in column B associated with dept A: =SUMIF(A:A,"A",B:B) -- Best Regards, Luke M "Flo" <Flo@discussions.microsoft.com> wrote in message...

COUNTIF Range
Does the COUNTIF range have to be a continuous range (i.e. A1:E1) or could it be a non-continuous range of cells (i.e. A1,D1,G1,J1,M1)? I want to count the number of occurances of a cell value being > 75 in the non-continuous range of A1,D1,G1,J1,M1. The COUNTIF function does not like the non-continuous range. Is there a better way of doing this. It is not an option to move the columns of data so they are a continuous range. Mike -- mluetkem ------------------------------------------------------------------------ mluetkem's Profile: http://www.excelforum.com/member.php?action=...

COUNTIF with or + and variables
I'm having a hard time figuring this one out My challenge is to count if column [I] is "A" or "B" and column [K] and [O] are "C" I'm trying to count all the ones in column [J] that meet the specific conditions. I tried an array but it didn't seem to take. Any hints are greatly appreciated. Try this… =COUNTIF(I:I,"A")+COUNTIF(I:I,"B")+COUNTIF(K:K,"C")+COUNTIF(O:O,"C") Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Dances...

Countif
I have 2 columns A & B with the following values: A B 1 4 1 2 5 2 3 6 3 How can I count the number of cells that A=3 with B="" using excel built-in functions? How can I combine the 2 criteria? Thanks A Lot!! Frank =sumproduct((A1:A10=3)*(B1:B10="")) "frank716" <frank716.t8ewn@excelforum.com> wrote in message news:frank716.t8ewn@excelforum.com... > I have 2 columns A & B with the following values: > > A B > 1 4 > 1 > 2 5 > 2 > 3 6 > 3 > > How can I count the number of cells that A=3 with B="&...

COUNTIFS with OR
Hi I have a sheet as below which is around 1000 rows long. Column A contains upto 100 different site codes. Column B always has 2 letters and there are 40 different combinations of the 2 letters possible. I am trying the following (which does not work obviously): COUNTIFS(A:A,1223,B:B,("Red" OR "Blue" OR etc etc)) A B 1 1223 Red 2 1223 Blue 3 1234 Blue 4 1223 Green 5 1235 Orange I know I could use COUNTIF+COUNTIF+.... multiple times but I would need 20 added together and want to simplify the formula. I am now losing the will to live looking for a...

Countif?
I'm looking for some help with the following; What I want to do in another cell is look up a range on column A and if its "text 1" count the number of "text 2" in column B. ie A1 B1 Text 1 Y Text 1 N Text 2 Y Text 2 Y This would result in For text 1 with Y = 1 For text 1 with N = 1 For text 2 with Y = 1 For text 2 with N = 0 Any help would be appreciated. Thanks, Teeb -- teeb ------------------------------------------------------------------------ teeb's Profile: http://www.excelforum.com/member.php?action=getinfo&...

SUMPRODUCT or COUNTIF??
All, I have been working on this for ages and can't get it right, and yet i feel it should be really simple! Can anyone help? I have an excel workbook which has 2 worksheets. On worksheet 1 (called 2005Stats) is a table: ColumnA ColumnB 01/01/2005 Placed 10/01/2005 Placed 15/01/2005 Open 16/02/2005 Closed 23/06/2005 Placed ColumnA has over 100 records with all different dates of this year, ColumnB has either "Placed", "Closed" or "Open" On worksheet 2, called 2005Calcs i want to have...

Countifs
Hi All, New in Excel 2007. Why is Countifs giving an error when I try to enter it with combined formulas like: =countifs(month(range);1;other_range;1) With kind regards, JP >=countifs(month(range);1;other_range;1) SUMIF SUMIFS COUNTIF COUNTIFS AVERAGEIF AVERAGEIFS These functions can only handle "straight" comparisons. That is, you can't manipulate a range array to test for a condition. In the formula above you're trying to manipulate the range array by first testing for the month. MONTH(range) = 1 The test has to be a "...

COUNTIF question
Hey all, i'm stuck. I need to do a formula that will count the number of cells in column 'A' only if the corresponding cell in column 'B' has a value of '0'. =COUNTIF(O:0,"0")+COUNTIF(P:P,">0") - just counts both, and that's as far as i have got. Can anyone help me please. Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ This is probably a cheats way of doing it but... I would create a third column with...

Countif(and
I want to count cells in Column C when column A criteria are met and Column B criteria are met. ex. Countif(AND(A1:A5,M3,B1:B5,M4,COUNT(C1:C5) =SUMPRODUCT(--(A1:A5=M3),--(B1:B5=M4),--(C1:C5<>"")) -- David Biddulph "John" <John@discussions.microsoft.com> wrote in message news:EE92CF58-B41A-49F3-BE6E-00567F3DF568@microsoft.com... >I want to count cells in Column C when column A criteria are met and Column >B > criteria are met. > ex. Countif(AND(A1:A5,M3,B1:B5,M4,COUNT(C1:C5) John, It's not entirely clear what you mean ...

COUNTIF formula?
Hi, I=92m using Excel 2002 and I have a question about using a formula to determine if certain words are found within a cell. For example, I want to determine if several words are found in each cell in column A and, if so, I want to put the word =93Yes=94 in the corresponding cell in column B. If the word isn=92t found then I just want that cell in column B to remain blank. So, in the following example, I want to see if the following words: Oranges Apples Lemons are found in any of the cells in column A. The result would look like this: Column A Column B Apples, Oranges, Pears Yes ...

Sorting and COUNTIF
This may be a double post; apologies. This problem seems to happen only when my RANGE is on a different worksheet than my CRITERIA. When I run a COUNTIF, if I sort the table, the criteria returns the count of whatever is in the position of where the CURRENT criteria USED to be. I don't want that. I never know how to explain relative and absolute references, so let me give you an example. Sheet 2 has an inventory of four items: Hard-boiled eggs, oil filters, Jonas Brothers posters, and dry-erase markers. For simplicity, let's call it "Sheet 2! Range". I...