[Sovled] Question Count fomula?

[Gleowine]

Hello,

I have been using this formula

=COUNTIF(Master!I:I,'Source Lists'!A1) It is counting trends for the year, in a summary tab. (this works)

(Master is a tab with all the daily information for incidents, The Source list is a seprate tab where I put my pull down menu information, and this is going in a thrid tab called trends. )

The "master" tab has a date column 01/11/12 format I would like to set up the summry tab by month is there a way to add that to this formula?

Currently the trend tab/sheet looks like this

......................Total
MUI - Verbal ......5
MUI - Physical.....0
MUI - ***...........0


I want to track trends monthly and quarterly

...................... Jan.... Feb ...March .... Total
MUI - Verbal........2.....3........0
MUI - Physical .....0.....0........0
MUI - *** ...........0.....0.......0

I hope I have given enough inoformation, Thank you, Excel 2010

[BB1972]

Gleowine, welcome to the forum.

You should upload a small sample file for us to work on - while we are happy to help, a lot of members are disinclined to create your data-set as a precursor to assisting you.

[Gleowine]

Okay I stripped out most of the tab's that did not related and data due to confidentiality.
Let me know if you need more data, but on the trend tab you can see I set up one formula for MUI – Verbal for the annual count, I would like a formula for a monthly count (I can sum the total for quarterly after that.)

Thank you for any help you can provide.

[FDibbins]

tryu this, copied down and across...
=COUNTIFS(Master!$B$6:$B$9,">="&B$1,Master!$B$6:$B$9,"<="&C$1,Master!$I$6:$I$9,Trends!$A2)

[Gleowine]

It returns 0, not sure if I'm doing something wrong. should equal 2 in January in the sample data right?

[FDibbins]

grrr sorry, my bad in the sample file ypu posted, i changed you're dates from jan, feb, mar etc to 1/1/12, 2/1/12, 3/1/12 etc, and then cutom formatted them to "mmm"

Jan, Feb, Mar etc are not actually dates, they are text, and as such, it makes it hard to relate them back to actual dates

[Gleowine]

Thank you, very much

[Gleowine]

Okay It worked but a few issues, I did change the formula on the counts, but I also put your fomula in oure on the second row down. Sorry please look at the trends tab MUI - Verbal.

The annual count is right 12, but the months are off, I added the fomula to my full data and noticed some counts did not match. I went in and simplifed the data to this just using 1/1/12, 2/1/12 and it started added up wrong?? any thoughts?

Could I manual set a Month column by text or number when entering say Jan or 1 in the column would that help instead of using dates?

[Gleowine]

I tried not using dates just numbers and abc... it keeps adding up to 2 for each month and skiping last month...

[FDibbins]

i spotted 2 problems with you're formula.

1. my bad, i used >= and <=, it should have been >= and < (else the 1st of the next month gets included in the count.

2. you removed the $ (absoluting) from the formula, so that when it's copied, the column references incorrectly change.

use this, copied down and across...
=COUNTIFS(Master!$B:$B,">="&B$1,Master!$B:$B,"<"&C$1,Master!$I:$I,Trends!$A2)

[Gleowine]

Thank you, That worked and now I know what the $ does.

I hate to push my luck, you been so helpfull

Is it possible to add a second criteria lTrends!$A2 picks all 'MUI-Verbal'
If I make a new tabs and do one just for each county and set up a county list could I have all the same Criteria but with added a county sort? I can set up a new table if needed.

Thank you,

[FDibbins]

im pretty sure we can do the extract/summary any way you want

can you upload a sample of what you want, and i will see what I can come up with for you?

[Gleowine]

Hello,

I up load a new file it has a tab called 'Hamilton' I just copied the trend tab, I would like the same information in the Hamilton tab but have it sort by Hamilton from the County column in the master tab.

If possible then I can set a tab up for each county.


Thank you

[FDibbins]

try this, copied across and down...
=COUNTIFS(Master!$B:$B,">="&B$3,Master!$B:$B,"<"&C$3,Master!$I:$I,$A4,Master!D:D,Hamilton!$B$1)

for the quarters, use this copied to each qtr and down
=SUBTOTAL(9,B4:D4)
and for the total, use
=SUBTOTAL(9,B4:Q4)
copied down

[Gleowine]

Worked, thank you so much.