Conditional count on large amount of data 2

[Rori]

With the data below (which is just a very small section used for example) we are trying to find a way, where in a single cell we can display a count of the amount of entries that meet our conditions. For example: We want to limit the count to entries with Type 11, Wheel MT and Colour Yellow. (In this case [2])

I've tried working with the tricks I know in Excel and also bugged a few other people who are pretty clever. So far unless we add an additional column to do the conditional yes/no, we're unable to do it.

Any takers?

Thanks in advance.

Rori


Type Wheel Colour
01 RN Green
01 MT Yellow
01 MT Yellow
01 MT Yellow
01 MT Yellow
01 MT Yellow
01 MT Yellow
11 RN Green
11 RN Yellow
11 RN Green
11 RN Yellow
11 MT Yellow
11 MT Yellow
13 MT Yellow
13 MT Yellow
13 MT Yellow
13 MT Yellow
13 MT Yellow
13 MT Yellow
13 MT Yellow
13 RN Yellow
13 MT Yellow
13 MT Yellow
13 MT Yellow
13 MT Yellow
15 RN Green

 

[Griffyn]

You could write a custom function that, on recalc, does the work you want via a For...Do loop. I'd prefer to put in the extra column and then hide it.

 

[mrsnrub]

Have you tried the subtotal function?

Insert a row above your data.
In the cell A1 (assuming the 'Type' column is column A) type the following:

=SUBTOTAL(2, A3:A1000)

(change the 1000 to however many rows you have)

Now apply a filter to your heading row and apply the filter to show only the conditions you require.
The contents of the subtotal cell will change as you apply the filter.

Hope this helps.

 

[KenWright]

Easy enough to do with SUMPRODUCT:-

=SUMPRODUCT((A2:A1000=11)*(B2:B1000="MT")*(C2:C1000="Yellow"))

or you can substitute the values for cell references if you have your values in other cells, eg:-

=SUMPRODUCT((A2:A1000=F1)*(B2:B1000=G1)*(C2:C1000=H1))

Regards
Ken.................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

or even easier - pivottable.
1 result cell, 3 page fields - dropdowns control the result cell

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

LOL - Anytime I see a list like that, I drool to throw a pivot table at it and then watch someones face as I summarise their data in a hundred different ways in minutes

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

s'funny - the 1st time you lob a pivottable over some data for someone and they just sit there dumbfounded with the occasional "that would'ha' taken me hours" or "I used to count them by hand"

Even better is when you show them about page fields and restricting items - our finance team thinks I'm a hero just because I showed them about pivottables - their month end accounts take 2 days now rather than 2 weeks !!!

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Geoff - typo in your sig?? 'thjat'

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

Ken - irony - considering the rest of the content !

However, it's about time to switch error haikus so time to browse for a new one !

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

LOL

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Rori]

Thanks everyone for your efforts. The data I provided was to give an insight into the problem without confusing the issue. In truth we're talking about x0,000 records and about 12 or more different fields of data, including dates, unique's and yes/no type data. The data will change regularly as it's updated so I was wanting to have a solution that was single cell. Pivot tables have worked so far, but the data is to be arranged for a static weekly report to be printed for the higherups. Another workmate has offered the suggestion of

{=SUM(IF(A:A=10,IF(B:B="MT",IF(C:C="Lapsed",1,0))))}

however Ken's suggestion works brilliantly. The fact it's not nesting functions gives us lots of control.

Again, thanks for your help everyone. Really appreciated.

Rori

 

[xlbo]

the formula there is an array formula - very high calc overhead - easier to use SUMPRODUCT

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[SQLScholar]

or maybe try a dsum

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!