This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(310.4, 97%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
Having issues getting an excel formula to work. Across two sheets within a register, I need to match rows (Column B3:B235 and Column B3:B235 in Sheet 2 ('Risks') in a formula in a cell in Sheet 1 (to be drag down so calculations occur for all business areas). These columns have different business area names and I want to be able to count the number of risks for each business area with a risk rating of "Low". There is a third column in Sheet 2 that has the risk ratings. I've tried IF, Count IF, and SUM formula variations but can get it quite right. I need to count if B3:B235=Risks!B3:B235 (match the same business area rows across the two sheets) AND Risks!H3:H235=Low (a column with different risk rating levels). This came close but the first part of the formula didn't match rows with the same business area name, it just counted all cells with an entry, =IF(B3:B235=Risks!B3:B235,COUNTIF(Risks!H3:H235,"Low"))
=SUMPRODUCT((B3:B235=Risks!B3:B235)*(Risks!H3:H235="Low")) isn't counting accurately? It doesn't seem like the business area name lines are matching properly. The business area name should occur once on the first sheet and multiple times on the second sheet.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
For any given row in the first sheet, you want to count the number of rows in Risks that match just this name and Low. Enter the following formula in the row 3 cell of the first sheet column:
=COUNTIFS(Risks!B$3:B$235,B3,Risks!H$3:H$235,"Low")
You can then copy this down to as many first sheet rows as needed