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(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I’ve been working on a formula for an Excel spreadsheet for several months. What I’d like the formula to do is add up all the Critical’s, High’s, Medium’s, and Low’s on the STATS-HOST-SEVERITY tab and put the totals on the SCORECARD tab in their respective fields (put the total of Critical’s from the STATS-HOST-SEVERITY tab, in the cell to the right of Critical on the SCORECARD tab, the total of High’s on STATS-HOST-SEVERITY to the right of High’s on the SCORECARD tab, & so on).
However, sometimes one or more of the Critical’s, High’s, Medium’s and Low’s don’t always show up on the report. So I need the formula to look to see if this field is present. If it is, then SUM / count how many there are of that particular category and put that total on the SCORECARD. And if there isn’t any, then put a zero in that category.
These are a couple formulas I’ve tried that I thought for sure would work. But they’re still not giving me the correct answer.
=SUMIF('STATS-HOST-SEVERITY'!B2:E2,"High",'STATS-HOST-SEVERITY'!C3:C33)
=SUMIF('STATS-HOST-SEVERITY'!B2:E2,SCORECARD!A5,'STATS-HOST-SEVERITY'!B:F)
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Replace your 1st formula with this one
=SUMPRODUCT(('STATS-HOST-SEVERITY'!B2:E2="High")*('STATS-HOST-SEVERITY'!B3:E33))
Replace your 2nd formula with this one
=SUMPRODUCT(('STATS-HOST-SEVERITY'!B2:E2=ScoreCard!A5)*('STATS-HOST-SEVERITY'!B3:E33))
Hope it helps..
You are welcome. Try this
=SUM(drop(XLOOKUP(A5,'STATS-HOST-SEVERITY'!$B$2:$E$2,'STATS-HOST-SEVERITY'!B3:E33),-1))
Thank you so much for your help with the formula. Your formula worked great. On the "STATS-HOST-SEVERITY" page, I have a "Grand Total" row. Since my report doesn't always have the same amount of rows each time I run it and I don't want to add that into the total. How can I make sure Excel doesn't include that row when it runs the formula? Do I need to add something like:
-('STATS-HOST-SEVERITY'!'Grand Total')
I did try it, but it didn't work.
Hi,
In cell B5 of the Score card worksheet, enter this formula
=SUM(XLOOKUP(A5,'STATS-HOST-SEVERITY'!$B$2:$E$2,'STATS-HOST-SEVERITY'!B3:E33))
Hope this helps.
Thank you both of those worked. I really appreciate your help.