Using COUNT IF formula while SUM another for one TOTAL

[Ianseconds]

Good Morning,

I'm currently working on a spreadsheet for job openings and ran into a problem.

Cell A21 of my attached spreadsheet is titled TOTAL POSITIONS, with a numeric count to the right of positions I will be tracking. In Column A, I will have 4 text values. Open, Hold, Pending & Cancelled. I used a =COUNTIF(e6:e23,"Pending") to count only pending values. I do not want to count Cancelled. Now, the easy question, how do I add it to this formula to also count, as values, Open & Hold?

Next, Column B of my spreadsheet is a numeric value of how many positions are actually open. Most of these values are 1, but some are 2 or 3. If all of these values were 1, my COUNT IF formula would be fine in cell B21 and I would be done, but how can I reflect the additional positions in my total count in cell B21?

So...basically, I need cell B21 to be the total number of positions that are in column b, but filtering out all Cancelled from column A.

Thank you for your help. I will be monitoring for quick replys to all.

[Ace_XL]

Try

=sum(COUNTIF(e6:e23,{"On Hold","Open","Pending"})

if all are 1

If you have other values in column B

=SUM(SUMIF(e6:e23,{"On Hold","Open","Pending"},b6:b23)

[JieJenn]

Enter this formula with Ctrl + Shift + Enter

=SUM(IF(($A$2:$A$19={"Pending","On Hold","Open"}),1,0))

[Ianseconds]

Thank you, Ace XL & JieJenn. The second formula that Ace provided worked perfectly. Much appreciated! You saved the day!