counting values from two different columns for epidemiology thesis

[maccer123]

Hello guys, I can't figure out how to count multiple values from two different columns and I really don't use excel too often but my thesis is forcing me to, so it is a must to figure this out.

All I want to know is the occurrences of diseases to a particular year. I have an excel file that has a messy record of diseases from our local hospital and it's my job to organize it by year (as my adviser wants me to) and the point of this is that so I can make some sort of graph for the defense of my thesis. Like for example, how many people got diagnosed with DENGUE in the year 2004 and the value DENGUE and the value 2004 are in separate columns. Does anyone know how to make a formula for this? I really am only familiar with the basics; I don't know how to make functions D:

 

[ghrain22]

Ok, we need to strip the year out of that cell.
Add a new column next to it and place the formula:

=year(k1)
Then copy it down.

then use this new column in the count ifs formula instead of k.

 

[maccer123]

it shows #### when I put that formula. would you want to see the file so you can help me figure it out better?

 

[ghrain22]

the ### just means you need to widen the column to see the value. (The value is longer than the column is wide)

 

[maccer123]

i widened it and it showed #VALUE!

This is how my file looks like

 

[ghrain22]

ok so the year function doesn't like that format.

We still need to pull the year out of that column some how.
1. Copy your date column and paste it in a new(temporary) sheet.
2. Highlight the data
3. Click the Text-To-Columns in the Data tab of the Ribbon.
4. Select Delimited>Next
5. Check Other, then type a space into the text box next to it. Then Click Finish.

What this should do seperate the parts of your date into seperate columns. You should now be able to copy paste the column with the year value in it, back into your Original Data Set.

 

[maccer123]

I did what you said to separate the year and did the COUNTIFS again but this happened:

 

[ghrain22]

your images arent showing up for me.
If your new Year column is in column H and disease is in column I:

Countifs($H$1:$H$10,"= 2004",$I$1:$I$10,"=Dengue")

 

[maccer123]

I don't know why it's not showing, but here http://s11.postimg.org/rrmw3xnoj/excr1.jpg
I tried that too, it yielded 0 once again :<

 

[ghrain22]

Company firewall....

I accidently had an unecessary space in the equation of my last post:
Countifs($H$1:$H$10,"=2004",$I$1:$I$10,"=Dengue")

That should work. if it doesn't try a each condition individually to see which one isn't returning correctly. And make sure there arent any additional spaces in your dates or disease name/type.

 

[maccer123]

hold on, so what you're trying to say is that each column should only have like 1 value and spaces? cause there are a lot of phrases in the disease diagnosis column