Formula with severals needs

Cynthia Guay

Hello Community ! 

i'm working on a formula to count how many machine of a brand have been sold. so i start with : 

=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New")

this formula is working well. 

But i want to add specific conditons to my formula and this is where it's not working when i'm writing it. Can you help : 

To be count it need to be : 

• New ( from column NEW OU USED)
• Yanmar ( from column Brand)
• "Current "or "in order" ( from my status column)
• and between a range of date, between nov 1 2022 to 31th october 2023 ( from my Date column) 

Can you help me please.

thanks a lot.

KPH

You are almost there @Cynthia Guay Just keep going with conditions in your COUNTIF.

You already have the first two things you need

• New ( from column NEW OU USED)
• Yanmar ( from column Brand)

With this, your current formula:

=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New")

In my sample of your data, this formula counts the 6 rows in yellow.

Next, you need to add another condition for the Status, but this one needs an OR in the criterion to count both "Current" and "In order" statuses, to meet your third requirement:

• "Current "or "in order" ( from my status column)

For this, you use:

Range: Status:Status

Criterion: (OR(@cell = "Current", @cell = "In order"))

So the formula becomes this (new part in bold):

=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")))

In my sample, this gives you 4 rows.

Finally, we need another two conditions for the date range.

• and between a range of date, between nov 1 2022 to 31th october 2023 ( from my Date column) 

Firstly, add a condition for on or after November 1, 2022.

For this, you use:

Range: Date:Date

Criterion: >=DATE(2022, 11, 1)

So the formula becomes this (new part in bold):

=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")), Date:Date, >=DATE(2022, 11, 1))

In my sample, that results in 3 records

And then, add one last condition, for on or before October 31, 2023.

For this, you use:

Range: Date:Date

Criterion: <=DATE(2023, 10, 31)

So the formula becomes this (new part in bold):

=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")), Date:Date, >=DATE(2022, 11, 1), Date:Date, <=DATE(2023, 10, 31))

In my sample, that results in 2 records.

KPH

You are almost there @Cynthia Guay Just keep going with conditions in your COUNTIF.

You already have the first two things you need

• New ( from column NEW OU USED)
• Yanmar ( from column Brand)

With this, your current formula:

=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New")

In my sample of your data, this formula counts the 6 rows in yellow.

Next, you need to add another condition for the Status, but this one needs an OR in the criterion to count both "Current" and "In order" statuses, to meet your third requirement:

• "Current "or "in order" ( from my status column)

For this, you use:

Range: Status:Status

Criterion: (OR(@cell = "Current", @cell = "In order"))

So the formula becomes this (new part in bold):

=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")))

In my sample, this gives you 4 rows.

Finally, we need another two conditions for the date range.

• and between a range of date, between nov 1 2022 to 31th october 2023 ( from my Date column) 

Firstly, add a condition for on or after November 1, 2022.

For this, you use:

Range: Date:Date

Criterion: >=DATE(2022, 11, 1)

So the formula becomes this (new part in bold):

=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")), Date:Date, >=DATE(2022, 11, 1))

In my sample, that results in 3 records

And then, add one last condition, for on or before October 31, 2023.

For this, you use:

Range: Date:Date

Criterion: <=DATE(2023, 10, 31)

So the formula becomes this (new part in bold):

=COUNTIFS(Brand:Brand, "YANMAR", [NEW OU USED]:[NEW OU USED], "New", Status:Status, (OR(@cell = "Current", @cell = "In order")), Date:Date, >=DATE(2022, 11, 1), Date:Date, <=DATE(2023, 10, 31))

In my sample, that results in 2 records.

Cynthia Guay

@KPH , ho my god ! You can't imagine how long i work on it to make it work and i was way far from your result. Thank you so much for taking the time + with all the explanations, i can't wait to apply it :D.

KPH

You were 40-50% of the way there @Cynthia Guay and just needed a little nudge in the right direction. I hoped an explanation would be more helpful than an answer - now you can apply the same process to any new formula and you'll be flying! Good luck!