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]

look into a countifs formula:
COUNTIFS function - Excel

 

[maccer123]

yes but how do you do it? if for example my column I is full of a variety of values (malaria, meningoencephalitis, dengue, etc.) and my column K is full of dates (Jan 1, 2004 - May 12, 2014), how'd you make a formula that would could DENGUE values in 2004? many thanks

 

[ghrain22]

Also another thing to look into might be a pivot table. This could potentially organize your data quite nicely especially once you implement slicers/fliters

 

[maccer123]

I'm not that much of an advanced user in excel I'm sorry :< could you be kind to tell how do you do the pivot table or formulas, please? i'd be very grateful!

 

[ghrain22]

yes but how do you do it? if for example my column I is full of a variety of values (malaria, meningoencephalitis, dengue, etc.) and my column K is full of dates (Jan 1, 2004 - May 12, 2014), how'd you make a formula that would could DENGUE values in 2004? many thanks

The link I provided explains it quite well.

countif($K$1:$K$10,"="&2004, $I$1:$I$10, "="&Dengue)

 

[ghrain22]

I'm not that much of an advanced user in excel I'm sorry :< could you be kind to tell how do you do the pivot table or formulas, please? i'd be very grateful!

A pivot table would just sumerize the data nicely.
1. If you add a column to your data with the header count.
2. Fill the count column value of 1 all the way down your data series.
3. Select all of your data
4. go to the instert tab
5. Click pivot table then Ok
6. Drag your date and Disease Field Lists to the Row Label Section
7. Drag your count Field list to Values section.


You can also create charts off this table

 

[maccer123]

okay I did it but it shows 0 when I do =COUNTIFS(I1:I85162,"=DENGUE",K1:K85162,"=2004")
is there something wrong with this?

 

[ghrain22]

okay I did it but it shows 0 when I do =COUNTIFS(I1:I85162,"=DENGUE",K1:K85162,"=2004")
is there something wrong with this?

Is K shown as a full date? ie. 1/1/2004?

 

[maccer123]

Yes but in this format: [TABLE="width: 162"]
<tbody>[TR]
[TD="width: 162"]Dec 25 2004 9:04AM[/TD]
[/TR]
</tbody>[/TABLE]
it says General