Counting the number of items on an invoice?

[Ben1985]

Hey guys,

I would like to know how many items on on each invoice. Unfortunately my sales report shows all the items in one big long list. Is there a formula I could use in a helper column that would work this our for me?

Any help appreciated,

Sample attached.

Thanks,

sampledata_invoicecount.xlsx

[etaf]

you can add this to a new sheet 2 cell A2
=INDEX(Sheet1!$A$3:$A$400, MATCH(0, COUNTIF($A1:A$1, Sheet1!$A$3:$A$400), 0))
and use Shift+control+enter to make an array formula and get {} around the formula

that will extract a unique value from the invoice numbers

then in B2 put
=COUNTIFS(Sheet1!$A$3:$A$400,Sheet2!A2,Sheet1!$E$3:$E$400,"<>Standard postage",Sheet1!$E$3:$E$400,"<>",Sheet1!$E$3:$E$400, "<>NEXT WORKING DAY - Order <2pm")

this will count the items for each invoice number and exclude - blank lines, standard postage and next working day

BUT i dont know how many line items you have that you want excluded - see attached
unless theres a column thats i can use thats identifies the items you want to count

i notice you have a category column that is showing postage (includes standard .... and next working....) - is that applicable to all other types you dont want to include - we could use that to simplify
=COUNTIFS(Sheet1!$A$3:$A$400,Sheet2!A2,Sheet1!$C$3:$C$400,"<>postage",Sheet1!$E$3:$E$400,"<>")

[Ben1985]

Hi thanks for the help.

Is there a more simpler way just to count all the items on the invoice? I can just minus the 1 of this total as every invoice will have shipping on it.

Thanks,

you can add this to a new sheet 2 cell A2
=INDEX(Sheet1!$A$3:$A$400, MATCH(0, COUNTIF($A1:A$1, Sheet1!$A$3:$A$400), 0))
and use Shift+control+enter to make an array formula and get {} around the formula

that will extract a unique value from the invoice numbers

then in B2 put
=COUNTIFS(Sheet1!$A$3:$A$400,Sheet2!A2,Sheet1!$E$3:$E$400,"<>Standard postage",Sheet1!$E$3:$E$400,"<>",Sheet1!$E$3:$E$400, "<>NEXT WORKING DAY - Order <2pm")

this will count the items for each invoice number and exclude - blank lines, standard postage and next working day

BUT i dont know how many line items you have that you want excluded - see attached
unless theres a column thats i can use thats identifies the items you want to count

i notice you have a category column that is showing postage (includes standard .... and next working....) - is that applicable to all other types you dont want to include - we could use that to simplify
=COUNTIFS(Sheet1!$A$3:$A$400,Sheet2!A2,Sheet1!$C$3:$C$400,"<>postage",Sheet1!$E$3:$E$400,"<>")

[Harini_Tiger]

Hello ,

You can just use pivot function to know the invoice details as shown in the attached excel. Let me know if you need any further assistance.

Regards
Harini.S

[Ben1985]

Hi thanks for the help.

Is there a more simpler way just to count all the items on the invoice? I can just minus the 1 of this total as every invoice will have shipping on it.

Thanks,

you can add this to a new sheet 2 cell A2
=INDEX(Sheet1!$A$3:$A$400, MATCH(0, COUNTIF($A1:A$1, Sheet1!$A$3:$A$400), 0))
and use Shift+control+enter to make an array formula and get {} around the formula

that will extract a unique value from the invoice numbers

then in B2 put
=COUNTIFS(Sheet1!$A$3:$A$400,Sheet2!A2,Sheet1!$E$3:$E$400,"<>Standard postage",Sheet1!$E$3:$E$400,"<>",Sheet1!$E$3:$E$400, "<>NEXT WORKING DAY - Order <2pm")

this will count the items for each invoice number and exclude - blank lines, standard postage and next working day

BUT i dont know how many line items you have that you want excluded - see attached
unless theres a column thats i can use thats identifies the items you want to count

i notice you have a category column that is showing postage (includes standard .... and next working....) - is that applicable to all other types you dont want to include - we could use that to simplify
=COUNTIFS(Sheet1!$A$3:$A$400,Sheet2!A2,Sheet1!$C$3:$C$400,"<>postage",Sheet1!$E$3:$E$400,"<>")

[Harini_Tiger]

hello,

let me know whether count formula works over? Please find the formula updated in the "data" tab in the cell A27. If this is not what you want could you please let me know what you want exactly to work over?

[etaf]

Is there a more simpler way just to count all the items on the invoice

what do you mean by simpler??

[Ben1985]

Hey, that is just counting the transaction numbers. Not what I need unfortunately. Thanks anyway.

hello,

let me know whether count formula works over? Please find the formula updated in the "data" tab in the cell A27. If this is not what you want could you please let me know what you want exactly to work over?

[Ben1985]

Hi, whenever I paste that formula into Cell A2 on the real report (=INDEX(Sheet1!$A$3:$A$400, MATCH(0, COUNTIF($A1:A$1, Sheet1!$A$3:$A$400), 0))

Excel wants me to open a new file? No idea why.

what do you mean by simpler??

[Ben1985]

Hi, whenever I paste that formula into Cell A2 on the real report (=INDEX(Sheet1!$A$3:$A$400, MATCH(0, COUNTIF($A1:A$1, Sheet1!$A$3:$A$400), 0))

Excel wants me to open a new file? No idea why.

what do you mean by simpler??

[Harini_Tiger]

In the sheet which i send over to you previously does not have sheet 1. if the file which you have does not have sheet 1 then obviously it will open a new file.

[Ben1985]

Hi Tiger,

I was missing something blatantly obvious, there is a qty field in the actual report so I've just used match formula to link it up with the corresponding transaction. =IF($C4="","",INDEX('RMS Sales'!$F:$F,MATCH($C4,'RMS Sales'!$Q:$Q,0),1))

Works a treat. Thanks for you help though.

[Ben1985]

Hi Tiger,

I was missing something blatantly obvious, there is a qty field in the actual report so I've just used match formula to link it up with the corresponding transaction. =IF($C4="","",INDEX('RMS Sales'!$F:$F,MATCH($C4,'RMS Sales'!$Q:$Q,0),1))

Works a treat. Thanks for you help though.