CODEWITHSUNDEEP
excelcountif
dffogt
dffogt

Reputation: 1

COUNTIFS on one range with a single cell corresponding to another range with several cells

For example:

http://i.imgur.com/41SS6l3.png

I would like to count an entry that is both greater than 60 and diagnosed with HTN. The problem is there may be multiple diagnoses and only seems to count those in the top row. So if I were to enter:

=countifs(A2:A16,"Greater than 60",B2:B16,"HTN")

It would only return "1", but if I moved HTN to the top row of the third entry it would count "2"

Upvotes: 0

Views: 241

Answers (2)

Michal Schmitt
Michal Schmitt

Reputation: 216

Excel treats your merged cell in ColA as 5 separate cells where your top cell has the data "Greater than 60" and the rest are blank. Since you're comparing a blank to whatever may be in the corresponding cell in ColB it won't meet the condition of your COUNTIFS(). One solution is to un-merge your first column, fill the blanks, and continue to use COUNTIFS(). Otherwise, macro can be written to accommodate your need.

Upvotes: 1

tc_NYC
tc_NYC

Reputation: 302

Surprisingly tough problem to solve using excel since you're checking for different conditions on different rows. IE over 60 might appear on row 2 and HTD might appear on row 3.

The way I would tackle this is writing some VB script. The below works:

Sub counter()

Destination = Range("C2").Address 'this is where the script will put the result

condition1 = "Greater than 60"

condition2 = "HTN"

'Please note, condition 1 (age) must be in column A of your spreadsheet and condition 2 (diagnosis) must be in column B.

'You can modify the code below if you want them in different columns

Range("B2").Select

counter = 0

For i = 1 To 500 'number of rows the script will check for

    If Range("A" & ActiveCell.Row).Value <> "" Then
        field1 = Range("A" & ActiveCell.Row).Value
    End If

    If field1 = condition1 And ActiveCell.Value = condition2 Then
        counter = counter + 1
    End If

    ActiveCell.Offset(1, 0).Select

Next i

Range(Destination).Value = counter

result = MsgBox(counter & " rows meet the specified conditions." & vbNewLine & " This result has been written to cell " & Destination & " on your spreadsheet")



End Sub

You can add this code to your spreadsheet by hitting alt+f11, in the project pane right click on the name of your workbook, then insert a module. You can then cut and paste this code in and run it in your workbook. Worth noting, I wrote the script to check for 500 lines, you can increase it if you want by changing the for i = 1 to 500 statement to how ever many rows this is checking.

Upvotes: 0

Related Questions