Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Richard pile
 
Posts: n/a
Default how do I count pairs of cells when each matches a condition?

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!
  #2   Report Post  
Posted to microsoft.public.excel.misc
Gary''s Student
 
Posts: n/a
Default how do I count pairs of cells when each matches a condition?

You can use an array formula or just a helper column:

In an un-used column enter:

=AND(A1="x",B1="y")*1 and copy down. Then just sum the contents of the new
column.
--
Gary's Student


"Richard pile" wrote:

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!

  #3   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default how do I count pairs of cells when each matches a condition?

For a single formula approach, try something like this:

For numbers in Col_A and text in Col_B

This formula counts to number of times Col_A contains the number 1 when
Col_B contains the letter "d":
C1: =SUMPRODUCT((A1:A10=1)*(B1:B10="d"))

Adjust range references to suit your situation.

Does that help?

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

XL2002, WinXP-Pro


"Richard pile" wrote:

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!

  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default how do I count pairs of cells when each matches a condition?

=sumproduct(--(a1:a10="x"),--(b1:b10=33))

Change the range--but don't use the whole column.

Richard pile wrote:

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.misc
pinmaster
 
Posts: n/a
Default how do I count pairs of cells when each matches a condition?

Try this:

if X is in C1 and Y is in D1 then
=SUMPRODUCT((A1:A10=C1)*(B1:B10=D1))
or
=SUMPRODUCT((A1:A10=x)*(B1:B10="y"))
where x = nemerical value (without quotes) and y = text (inside quotes) or
vise versa

HTH
Jean-Guy

"Richard pile" wrote:

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
count colored cells? DKY Excel Worksheet Functions 21 January 19th 06 10:47 PM
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 05:57 PM
Count cells based on date range in another column [email protected] New Users to Excel 1 May 5th 05 08:11 PM
how 2 Count number of cells that have specific condition format? daveydavey Excel Worksheet Functions 2 May 4th 05 02:06 PM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM


All times are GMT +1. The time now is 02:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"