CODEWITHSUNDEEP
excelexcel-formula
Bob
Bob

Reputation: 11

Excel: countIFS specific day of the week and additional criteria

I was wondering what would be a formula if I want to count based on various criteria, but specifically(because I think this is what I'm having trouble) if one of those criteria should be if a date of a column equals certain day of the week (i.e. 2/2/2016 = thursday)

So basically I want to count how many orders were made on a THURSDAY with the code FTN and with any status except ABANDONED (see image attachments)

Here is the formula I used which gave me 0 when the answer should be 5:

=COUNTIFS(A2:A15,WEEKDAY(A2:A15)=5,C2:C15,"FTN",D2:D15,"<>*ABANDONED*")

Data

Dates to day of week

Upvotes: 0

Views: 1619

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

COUNTIFS() is picky on how the criteria is done, you will not be able to do it this way. You will need to use an array type formula.

You can use SUMPRODUCT():

=SUMPRODUCT((WEEKDAY(A2:A15)=5)*(C2:C15="FTN")*(ISNUMBER(SEARCH("ABANDONED",D2:D15))))

This will count where all three are True.

Upvotes: 1

Related Questions