How to total how many times an item appears, with a condition

Hi!

I have two sheets in my spreadsheet. The first sheet (titled "Customers") shows a list of the customers (column A), the item they bought (in column B, each item is listed by its reference number) and whether they are to be shipped (column C - "yes" for shipping).

In the second sheet (sales) I have a list of all the items by reference number (column A) and a column (B) in which I want to put the total number to be shipped for the item.

I am struggling to find a formula that does what I want.

For column B in my second sheet, I can use the formula =COUNTIF(Customers!B:B,Sales!A2) to show me how many customers bought the item in total, but what I can't get it to do is to only count the customers who have a "yes" in the shipping column. So I want to count how many times the item appears but only if there is a yes in column C.

Any help will be greatly appreciated! Thank you.

If you use XL2007 or later you can try it with COUNTIFS (for multiple conditions):

=COUNTIFS(Customers!B:B,Sales!A2,Customers!C:C,"yes")

However, your profile shows that you are using XL2003, and COUNTIFS isn't available in that version. You could try SUMPRODUCT instead, though you can't use full-column references:

=SUMPRODUCT((Customers!B$1:B$5000 = Sales!A2)*(Customers!C$1:C$5000 = "yes"))

Hope this helps.

Pete

Thanks for any help you can give. Here's a sample spreadsheet, it's column B on the second page that I am struggling with.

Best Wishes,

lauracvpSample.xlsx

Perfect, that's it Pete! I have been struggling with this for ages so am VERY grateful to you!

You're welcome. Please update your profile (as you attached an .xlsx file).

Pete