Filtering for field with criteria of partial numbers

[MO21]

Hi everyone

I have a column in a spreadsheet that lists various invoicenumbers/ credit memo numbers

The first three digits of this number refer to the entity within the group who issued the invoice/ credit memo.

The second indicate whether it is a manual invoice (or manual credit memo) or automated one.

To illustrate:

    1232245678

    where 123 - entity code, 22 - manual vs automated indicator and the rest just part of inovice generated number

I need to provide a sum total for the following:

     - manual invoices (22 number in position 4 &5)

     - manual credit memo (24 number in position 4&5)

    -  automate invoices (anything other than 22 in position 4&5)

    -  automate credit memo (anything other than 24 in position 4&5)

Is there perhaps any tips on how I can create a filter formula / other tool I can use?

(Have been playing around with the filter tool but no luck yet 🙂

[bpatel]

hi @MO21 ,

here is what i came up with. 

if you could clarify these two steps that would be appreciated.

- automate invoices (anything other than 22 in position 4&5)

- automate credit memo (anything other than 24 in position 4&5)

i also attached the workflow. hope this helps!

[MO21]

Hi bpatel

Appreciate the response!  Looks like this could work.... but just to check how I can calculate the automated (and to answer your query)

Basically those two steps is the second part of the output I need.

To illustrate the final output:

- a) Total value of manual invoices 

- b) Total value of manual credit memos

- c) Total value of automated invoices

- d) Total value of automated credit memos

So the reason I have to be able to distinguish between invoice numbers that have '*22' and credit memo's that have '*24' is that these indicate manual (ie to sum A, and sum B as per list above). The invoice numbers that then do not have *22' and the credit memos that do not have '*24' are automated (and I need that to sum C and sum D)

[bpatel · Accepted solution]

 @MO21 

you can add a summarize tool to get the total sum. i am a still a little confused on the last two steps. How do you distinguish from invoices and credit memos if you are referencing everything besides 22 and 24.

[MO21 · Accepted solution]

Hi bpatel

Thanks!

Invoices would be a credit amount whereas credit memos would be a debit (in the accounting system).

I therefore can distinguish between them based on a positive vs negative value.

Will try and add this to my workflow and see how it runs!

Really appreciate the help - will respond accept as solution once I have a chance to run it later today.

Thanks!