Problems using COUNTIFS with multiple criteria and wildcard Hi, I\'m using COUNT

Question

Problems using COUNTIFS with multiple criteria and wildcard

Hi,

I'm using COUNTIFS to create a dashboard to summarise monthly order and quote data. Generating the information from data updating automatically from a SQL database.

I'm using COUNTIFS because I have multiple criteria.

Columns I'm using are:

Order Date (date ranges)

Order Type (order or reorder)

Quote Type (written, verbal, standard, special, demo etc) - this field can be a variety of any combination and the data displayed has comma separators i.e. example entries are:

,Written,

,Written,verbal,Notquoted

,Notquoted,Written,Standard,

So, I need to know how many written quotes for orders in a month.

I used COUNTIF to determine this data until I get to the Quote Type. This needs a wild card as I need to count all those with the word Written in the text.

I've tried using * and ? which don't return (I've checked and there are written quotes for the month I'm using).

The function works without this last section (quote type)..

This is how I'm building it:

=COUNTIFS('Order Data CRM'!Q:Q,">="&Sheet1!C38,'Order Data CRM'!Q:Q,"<="&Sheet1!C39,'Order Data CRM'!AD:AD,"="&Sheet1!P16,'Order Data CRM'!AG:AG,"?+Sheet1!P4?")

C38 Contains 01/02/2015

C39 Contains 28/02/2015

P16 Contains Order

P4 Contains ,Written,

All the above are on a separate calculation sheet which will be hidden..

Hope anyone can help!!

Explanation / Answer

Hi.

Try it this way::

=COUNTIFS('Order Data CRM'!Q:Q,">="&Sheet1!C38,'Order Data CRM'!Q:Q,"<="&Sheet1!C39,'Order Data CRM'!AD:AD,Sheet1!P16,'Order Data CRM'!AG:AG,"*" &Sheet1!P4 &"*")..

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.