Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances based on certain data
Given columns of data, I need to count occurances, based
on certain data. aa 0123 red 3 ab 0125 red 3 ac 0452 blue 5 ba 0478 white 12 bb 0845 white 1 bc 0654 red 54 I need to only look at data that starts with a "b" (first column), then I want to count how many colours (3rd column) have numbers less than "4" (last column). I suppose that the function should have the colour as an argument, so when it is called it will return the number of occurances. Can anyone help me please. Simon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances based on certain data
Simon
Try DCOUNTA You need column headers for your data. If it starts in cell A1 and looks like this: A B C D 1 Ref No. Colour Figure 2 aa 0123* *red ** 3 3 ab 0125* *red ** 3 4 ac 0452* *blue * 5 5 ba 0478* *white 12 6 bb 0845* *white 1 7 bc 0654* *red ** 54 Then you need to add criteria (I have assumed this is added in cells A11 to D12 as follows: A B C D 11 Ref No. Colour Figure 12 b* <4 I have included No. and Colour in case you want to get more specific with your criteria. The formula is then: =DCOUNTA(A1:D7,"Colour",A11:D12) and this gives me the answer "1". Hope that helps Andy *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Occurances | Excel Discussion (Misc queries) | |||
Count occurances in data | Excel Discussion (Misc queries) | |||
Count occurances | Excel Worksheet Functions | |||
Count of different occurances | Excel Discussion (Misc queries) | |||
count occurances | Excel Discussion (Misc queries) |