Help needed on COUNTIF function

[rajtak]

Hi,

I want to count the number of diseases for each year and input the result to the table highlighted below.

In 1996 has 4 counts of cancer which i want to input under the header Cancer for row 1996. Similarly 1991 has 1 count of dermatologic which i want to input under the header Dermatologic and so on for others as shown below.

I want this to be achieved using COUNTIF or any other function but not VBA script or Pivot Table.

Thank you in advance

 

[JamesW]

Hi,

Do you mean like this:

=COUNTIF(A1:A1000,B1)

Where A1 is your range, and B1 is the header "Cancer".

 

[rajtak]

I think was not clear in my question.

I want to count the diseases for each year from Column A and B.

Suppose Cancer is listed 4 times for 1996 and 2 times for 1997

I need that count in another table underneath the header 'Cancer' (As shown on image above) so 4 should be shown for 1996 and 2 should be shown for 1997

 

[rajtak]

Basically i need a count of diseases listed according to year

1996 - Cancer
1996 - Renal
1996 - Cancer
1997 - Viral
1997 - Cancer
1997 - Viral

As per the above example
1996 has 2 Cancer, 1 Renal
1997 has 1 Cancer, 2 Viral

 

[jasonb75]

Try

=sumproduct(--($A$2:$A$17=$C2),--($B$2:$B$17=D$1))

 

[rajtak]

Thanks Jason, this is exactly what I was looking for