Multiple criteria in countif

[Abarency]

I have a countif statement that works fine but I want to add another criteria set.

Current formula:

=COUNTIF(S21:U300,"[A] Cancelled Enroute") + COUNTIF(S21:U300,"[B] Cancelled Enroute")

I now need it to count as above only if the first character in O21:O300 is "2"

I've tried a couple sumproduct ideas and a dcount that was suggested but can't get anywhere with them.

Any help would be great.

[daddylonglegs]

Try this formula

=SUMPRODUCT((LEFT(O21:O300)="2")*ISNUMBER(MATCH(S21:U300,{"[A] Cancelled Enroute","[B] Cancelled Enroute"},0)))

[Abarency]

Worked perfect. Would you mind explaining it to me?

[Pepe Le Mokko]

Perhaps

Please Login or Register  to view this content.

[Haseeb Avarakkan]

Perhaps

Please Login or Register  to view this content.

Pepe,

I think it is a time for a coffee

Each additional range must have the same number of rows and columns as the criteria_range1

It must be Range, not arrays

[daddylonglegs]

Would you mind explaining it to me?

Using ISNUMBER and MATCH is a standard way to check a range against multiple values, so when you use

MATCH(S21:U300,{"[A] Cancelled Enroute","[B] Cancelled Enroute"},0)

That returns an "array" of values the same size as S21:U300, if the cell matches one or other of those text values then you get a number, otherwise #N/A, so when that's wrapped in ISNUMBER this part

ISNUMBER(MATCH(S21:U300,{"[A] Cancelled Enroute","[B] Cancelled Enroute"},0))

returns an array of TRUE/FALSE values depending on whether the cells match, then when you multiply by this:

(LEFT(O21:O300)="2")

which also returns a column of TRUE/FALSE values, you only get 1 when both are true, so you only need to sum the resultant array, every 1 represents a matched text value in a row where col O value begins with 2. SUM could be used rather than SUMPRODUCT but the latter doesn't require CTRL+SHIFT+ENTER

[Abarency]

Not to be a pain but look at this...

=SUMPRODUCT((LEFT(CAO!O21:O300)>"1")*ISNUMBER(MATCH(CAO!S21:U300,{"1*","2*"},1)))

Returns 125. Should be 126. There are 125 records with "2" and one "1"

[daddylonglegs]

Wildcards don't work in that part of the MATCH function - I'd use LEFT again, i.e.

=SUMPRODUCT((LEFT(CAO!O21:O300)>"1")*ISNUMBER(MATCH(LEFT(CAO!S21:U300),{"1","2"},0)))