Counting multiple cells with text

dwm16

New Member
Joined
Jan 21, 2004
Messages
3
I'm trying to find a way to count cells with text. Countif works if I'm only looking for one bit of text, but I can't figure away to do multiples. The tables below are a simplified version of what I'm trying to accomplish. Different people would enter text data using a validation list for each column, then at the end of the month, I want to count the number of occasions it was sunny and rainy in Fl, NY, and Tx in Jan, then Feb, etc.

I'm not sure if this makes sense or not, but i'm basically trying to write a formula that says: Countif (the data entry table has a "Jan" AND FL AND Sun) -- then put that count in the top left cell of the Jan "Results Table". Same with all the other cells in the results table (and there will be one results table for each of the 12 months).

Any help would be much appreciated. It doesn't have to be set up this way if there is an easier way.

Thanks.


DATA ENTRY TABLE
Jan FL Sun
Jan NY Rain
Jan FL Sun
Feb NY Rain
Feb NY Rain


RESULTS TABLE
JAN
Sun Rain
FL 2 0
NY 0 1
TX 0 0
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
use sumrpoduct() here

try this!
comb-perm2.xls
ABCDEF
1JanFLSunJan
2JanNYRainSunRain
3JanFLSunNY01
4FebNYRainFL20
5FebNYRainTX00
Sheet4
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
dwm16 said:
I'm trying to find a way to count cells with text. Countif works if I'm only looking for one bit of text, but I can't figure away to do multiples. The tables below are a simplified version of what I'm trying to accomplish. Different people would enter text data using a validation list for each column, then at the end of the month, I want to count the number of occasions it was sunny and rainy in Fl, NY, and Tx in Jan, then Feb, etc.

I'm not sure if this makes sense or not, but i'm basically trying to write a formula that says: Countif (the data entry table has a "Jan" AND FL AND Sun) -- then put that count in the top left cell of the Jan "Results Table". Same with all the other cells in the results table (and there will be one results table for each of the 12 months).

Any help would be much appreciated. It doesn't have to be set up this way if there is an easier way.

Thanks.


DATA ENTRY TABLE
Jan FL Sun
Jan NY Rain
Jan FL Sun
Feb NY Rain
Feb NY Rain


RESULTS TABLE
JAN
Sun Rain
FL 2 0
NY 0 1
TX 0 0

Are your months true date values or just text?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,091
Messages
5,768,039
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top