# Countifs with Or tidy-up

#### horizonflame

##### Board Regular
Hi,

Can anyone help condense this please?

Code:
``=COUNTIFS(DatesWork!\$C\$2:\$C\$3000,"GATED",DatesWork!\$G\$2:\$G\$3000,"01/01/1900")+COUNTIFS(DatesWork!\$C\$2:\$C\$3000,"GATED",DatesWork!\$G\$2:\$G\$3000,"#")+COUNTIFS(DatesWork!\$C\$2:\$C\$3000,"GATED",DatesWork!\$G\$2:\$G\$3000,"No Date")``
Thanks

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### JustynaMK

##### Well-known Member
Try something like that:

Code:
``=SUMPRODUCT(--(\$C\$2:\$C\$3000="GATED"),(\$G\$2:\$G\$3000=1)+(\$G\$2:\$G\$3000="#")+(\$G\$2:\$G\$3000="No Date"))``
Please note that I replaced "01/01/1900" with "1", as this is the first registered date in the system.

#### horizonflame

##### Board Regular
Thank you, that did the trick ?

#### jtakw

##### Well-known Member
Hi,

Try also this:

=SUM(COUNTIFS(DatesWork!\$C\$2:\$C\$3000,"GATED",DatesWork!\$G\$2:\$G\$3000,{"01/01/1900","#","No Date"}))

#### JustynaMK

##### Well-known Member
You're welcome jtakw's solution will also work perfectly fine. Cheers.

1,102,666
Messages
5,488,187
Members
407,631
Latest member
Mehezabin

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...