COUNTIF or COUNTIFS with multiple criteria

[rakotonirinas]

Hello everyone,

I would like to require some help from you please. Attached is a simple workbook with a 5 column data. The purpose of the the function is to calculate the total number of new order, new customer and amount received for a given reporting period.

I did manage to get the total of new order and amount paid but somehow I get stuck in getting the correct figure for the total number of new customer. As per the example only Sultro LTD is the new customer since Dimat LTS came on board the month before so should not be counted more than once. I thought using SUMPRODUCT may help but alas it didn't achieve the intended result.

Can you help please?

Much appreciated.

[alansidman]

How do you distinguish a new customer from an existing customer. Excel needs to know what the criteria is for a new customer.

[rakotonirinas]

For this instance it is the postcode as it is unique normally.

[dosydos]

First i sorted your data by Order Period.
Then i added a count function in column A that you can hide if you want.

Please Login or Register  to view this content.

Then i added another count formula in N5 for your answer.

Please Login or Register  to view this content.

[rakotonirinas]

I guess the procedure is will be something like:
- count how many customers first for the reporting period
- then check if the postcode is not duplicated
- The result would be: those non-duplicated emails where the order period is 01/12/2019

Not sure whether this makes sense

[rakotonirinas]

Thanks dosydos. This has done it. Awesome!

I have one question though. Can we integrate this formula within the N5 formula?