Excel Count Based On 'AND' Criteria Being Met

[Mictain]

Hello,

This is probably very simple, but I can't quite convert what seems logical into a formula to get the results needed.

I have two columns of data with this setup:

Cat. Time Range
A 30 to 60
A 60 to 120
A 60 to 120
B 180 to 240
B 300+
C 0 to 30
C 60 to 120

I need a formula to look at column 1, see if there are any A's present; if so count how many A's show '0 to 30', how many show '30 to 60' and so on, then move onto the B's and work right through the combination of letters and time ranges on the spreadsheet.

These counts will go into a results table showing the category / time breakdown across the board.

I've tried working with And and Countif (as they fit the logical route) but I'm having no joy creating a single formula to do this for me.

If anyone has any ideas on this that would be great. Thanks in advance.

Neil.

 

[Molby]

The easiest way is with a pivot table

 

[Mictain]

Thanks Molby. I've never created a pivot table before so I've had a quick bash at one and yes, it's displaying the category / breakdown count as required

The original request was to have the count displayed for all combinations in a results table at the same time, like this:

And my first attempt with pivot tables is giving me one count result at a time:

I don't know if this is something that can be changed? I know nothing about pivot tables!

If not, I'll just tell them to get the pivot info and enter it manually into the results table

Thanks again.

 

[Molby]

How have you set it up?

If you have say Cat as your rows and Time Range as your columns, then add time range into the data items, set as a count, then this should give you what you are after.

 

[KenWright]

It looks like you have dragged the fields into the PAGE fields.

When you create the PT, drag Cat to where it says

"DROP COLUMN FIELDS HERE"

and drag the Time Range to where it says

"DROP ROW FIELDS HERE"

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

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

 

[SkipVought]

Hi,

After you drag the TIME to the COLUMN AREA and finish the PivotTable, right click the TIME heading and select Group & Outline - GROUP and Group by whatever value, starting/ending at whatever value.

Skip,
[sub]
[red][/red]
[/sub]

 

[Mictain]

Thanks for all your help on this, folks. It's now looking more like I'd hoped:

I'm going to leave it alone now before I mess it up

Thanks again.

Neil.