# Countifs with multiple criteria

✭✭✭
edited 12/09/19

I currently have a countifs formula that is working great but I need to add one additional criteria to it.  I tried several different ways and I keep getting an error.  The formula is written to look at 3 different sheets to pull information.  Here is what is currently working:

=COUNTIFS({ACW Big Board Range 1}, [Primary Column]1, {ACW Big Board Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {ACW Big Board Range 2}, IFERROR(MONTH(@cell), 0) = 1) + COUNTIFS({ACW Customers Guns Range 1}, [Primary Column]1, {ACW Customers Guns Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {ACW Customers Guns Range 2}, IFERROR(MONTH(@cell), 0) = 1) + COUNTIFS({ACW Delivered Range 1}, [Primary Column]1, {ACW Delivered Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {ACW Delivered Range 2}, IFERROR(MONTH(@cell), 0) = 1)

This is what I want to add:

=COUNTIFS({ACW Big Board Range 3}, <>"Inventory")

I want to exclude any products that are marked as inventory and this will only effect the 1st countifs statement.  This formula works on its own but when I add it to the other one it gives an error. Any suggestions would be greatly appreciated.  Am I asking the formula to do too much?

Thank you,

Patty

• ✭✭✭✭✭✭

=COUNTIFS({ACW Big Board Range 1}, [Primary Column]1, {ACW Big Board Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {ACW Big Board Range 2}, IFERROR(MONTH(@cell), 0) = 1, {ACW Big Board Range 3}, <>"Inventory") + COUNTIFS({ACW Customers Guns Range 1}, [Primary Column]1, {ACW Customers Guns Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {ACW Customers Guns Range 2}, IFERROR(MONTH(@cell), 0) = 1) + COUNTIFS({ACW Delivered Range 1}, [Primary Column]1, {ACW Delivered Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {ACW Delivered Range 2}, IFERROR(MONTH(@cell), 0) = 1)

.

See the BOLD/UNDERLINED portion above.

• ✭✭✭

Paul,

Thank you!  This worked perfectly.

Patty

• ✭✭✭✭✭✭

Excellent! Happy to help!