Afternoon, really appreciate it if someone could help. trying to come up with a formula that will add cells and give one total for different refs. For instance we have a spread sheet with part numbers in column A which appear in various rows down the spreadsheet and then in a different column there is the quantities for those refs. the bit I'm struggling with is I only want to count the ones without the word legacy which in another column.

 Catalogue Ref Description Count Unit Price A055 Sofa 2 A083 Bench Seat 5 A019 Admin Desk 10 Legacy A019 Admin Desk 5 A055 Sofa 5 Legacy

So I would want to end up with only 5 for the ref A019 not 15 as there are 10 with the word legacy in the 4th column and only 2 of the ref A055 etc.

Hopefully that makes sense. It's only a very simple version of my spread sheet.

Look forward to someone's kind response.

2. You can use COUNTIFS() to count based on multiple criteria.

e.g.

Code:
`=COUNTIFS(A:A,"A019",D:D,"<>Legacy")`
will result in 5 because in only counts A019 in column A where column D does not contain "Legacy".

You can replace the "A019" reference with cell reference containing the lookup value...

3. For Total Count value
Code:
`=SUMIF(D:d,"<>Legacy",C:C)          (=12)`
For each individual Ref
Code:
`E2=IF(D2<>"Legacy",SUMIFS(C:C,D:d,"<>Legacy",A:A,A2),"/")`

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•