COUNTIFS with DISTINCT and YEAR formula

Seth Morth

I have a doozy of a formula I am stuck on!

I want to count the # of POs from this year (there are also POs from 2021) and the # of Line Items on those POs from this year. I know I am achingly close, but I am fried from the week and as stuck as I am tired.

See following pit of despair.

=COUNTIFS(DISTINCT(PurchaseOrder:PurchaseOrder) + " " + "distinct POs and a total of" + " " + COUNT(PurchaseOrder:PurchaseOrder) + " " + "total line items", OrderEntryDate:OrderEntryDate, YEAR(@cell) = TODAY())

Thanks in advance if you are able to help me!

Kelly Moore

Hey Seth

my bad, I left out a term

=COUNT(DISTINCT(COLLECT(PurchaseOrder:PurchaseOrder, OrderEntryDate:OrderEntryDate, IFERROR(Year(@cell),0)=YEAR(TODAY()))))+ " " + "distinct POs and a total of" + " " +COUNTIFS(PurchaseOrder:PurchaseOrder,<>"", OrderEntryDate:OrderEntryDate, IFERROR(YEAR(@cell),0)= YEAR(TODAY()))+" total line items"

That's what I get for just typing the text instead of testing first. See if this works now

Kelly

Mike TV

@Seth Morth

This will make the formula read cleaner in the cell:

=COUNT(DISTINCT(COLLECT(PurchaseOrder:PurchaseOrder, OrderEntryDate:OrderEntryDate, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))) + " " + "distinct POs" + CHAR(10) + COUNTIFS(PurchaseOrder:PurchaseOrder, <>"", OrderEntryDate:OrderEntryDate, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) + " total line items"

Then just make sure to turn word wrap on for the cell/column

Mike TV

@Seth Morth

"I want to count the # of POs from this year (there are also POs from 2021)" - This part I understand

"and the # of Line Items on those POs from this year." - What do you consider a "line item" from your screenshot?

What are the + " " + attempting to count or do in your formula?

Seth Morth

Hi Mike!

A line item is each row; I do not care about the Qty, and each row gets counted once. So a 'Line Item' is a gizmo whether we are buying 1 or 100 of them.

The +'s get text into the blend so the result will (ideally) appear as:

2,571 distinct POs and a total of 8,503 total line items

...that can be ignored and this is the barebones of the formula:

=COUNTIFS(DISTINCT(PurchaseOrder:PurchaseOrder) COUNT(PurchaseOrder:PurchaseOrder), OrderEntryDate:OrderEntryDate, YEAR(@cell) = TODAY())

Kelly Moore

Hey @Seth Morth

Let's see if this might work

=COUNT(DISTINCT(PurchaseOrder:PurchaseOrder, OrderEntryDate:OrderEntryDate, IFERROR(Year(@cell),0)=YEAR(TODAY())))+ " " + "distinct POs and a total of" + " " +COUNTIFS(PurchaseOrder:PurchaseOrder,<>"", OrderEntryDate:OrderEntryDate, IFERROR(YEAR(@cell),0)= YEAR(TODAY()))+" total line items"

Does this work for you?

Kelly

Seth Morth

Ooooo...super close Kelly! It is not counting the total # of POs correctly {I have to laugh, it says '1 POs and a total of 5849 line items}. The good news there is that is the correct # of line items this year. I'll hit this with coffee in the AM, but I really thank you for helping me get my syntax honed in more Kelly!

• Seth

Kelly Moore

Hey Seth

my bad, I left out a term

=COUNT(DISTINCT(COLLECT(PurchaseOrder:PurchaseOrder, OrderEntryDate:OrderEntryDate, IFERROR(Year(@cell),0)=YEAR(TODAY()))))+ " " + "distinct POs and a total of" + " " +COUNTIFS(PurchaseOrder:PurchaseOrder,<>"", OrderEntryDate:OrderEntryDate, IFERROR(YEAR(@cell),0)= YEAR(TODAY()))+" total line items"

That's what I get for just typing the text instead of testing first. See if this works now

Kelly

Seth Morth

Home run Kelly!

You are a wizard!

Yes that works, and I have learned the lesson with COLLECT, and hopefully DISTINCT as well. I really appreciate you taking the time to help me, I know it was a twisty bit of formula.

Thank you ever so much!

+ Seth

Mike TV

@Seth Morth

This will make the formula read cleaner in the cell:

=COUNT(DISTINCT(COLLECT(PurchaseOrder:PurchaseOrder, OrderEntryDate:OrderEntryDate, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))) + " " + "distinct POs" + CHAR(10) + COUNTIFS(PurchaseOrder:PurchaseOrder, <>"", OrderEntryDate:OrderEntryDate, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())) + " total line items"

Then just make sure to turn word wrap on for the cell/column

Seth Morth

Thanks Mike! That is a bit of fun! I may be presenting that with Word Wrap turned on in another Dashboard so I appreciate that morsel of tinkering!