Formula to use to count instances of in Excel

B

Bill

I am trying to set up a formula that will count the instances of specific
dates. Example: Only count the number of times that a cell in Column B
(B4=1) and a corresponding cell in Column C (C4=2) meet the required
parameters.

I have been trying to use the COUNTIF function but can not get it to work
with the AND function since the AND function does not seem to work well with
a range of data.

Any help would be appreciated.
 
R

Ron Coderre

Try this:
A1: (some value)
A2: (some other value)
A3: =SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A2))

Counts the combinations of Col_A=A1 and Col_B=A2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
S

SteveG

Bill,

Try this.

=SUMPRODUCT(--(ISNUMBER(FIND(DATE(2005,12,22),B1:B10))),--(C1:C10=DATE(2005,12,25)))

Just change the dates in the DATE function to whatever dates you are
looking for.


Cheers,

Steve
 
S

SteveG

Ron's is definitely a simpler version. I just started experimenting
with SUMPRODUCT so thanks Ron. I have seen this used many times on
this site but don't fully understand what the -- that preceeds the
conditions do. I've searched on the Help files with no luck. Any
quick way to explain? Is there a site that you could suggest?

Thanks,


Happy Holidays.


Steve
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count Number of Unique Cells That Don't Have Certain Characters 0
Using <= in countifs statement 1
Counting unique instances of a text string 2
countifs Function not giving desired result with date criteria 2
Use TODAY() function as the criteria in a SUMIF formula 1
Count Blank Cells with Conditions 3
Hyperlinks with lookups 0
Needs help combining functions. 3

Top