COUNT BASED ON MULTIPLE CRITERIA

[rhwebb]

Okay, I'm submitting a sample spreadsheet but I hope you understand my question. I'm trying to create a formula that will count based on two criteria (two separate columns). A customer number and order date are the examples provided. In short, If column A is a number AND column B is not blank then count. In the example the result should be 3. Hope this makes since and apologies but I cannot provide a copy of the actual file for security reasons. Thank you in advance.

 

[bebo021999]

If column B always date and blank cells:

=COUNTIFS($A$2:$A$100,">0",$B$2:$B$100,"<>")

 

[Peter_SSs]

In short, If column A is a number AND column B is not blank then count. In the example the result should be 3.

Going by your sample, this should give the same result?

=COUNT(B2:B100)

.. or do you actually have some dates in column B that do not have a customer number in column A?

 

[jtakw]

Going by your sample, this should give the same result?

=COUNT(B2:B100)

.. or do you actually have some dates in column B that do not have a customer number in column A?

Hi,

Was thinking the same.

Also, might you have Repeated Customer #s in Column A, with same and/or different Order Dates in Columns B, if so, how should those be counted?

 

[rhwebb]

Thank you all. I was able to find a solution by using =countif(b:b,"*/*"). On my original sheet there is a number in the date header so it was counting that. So for my case by selecting the "/" that is only located in the cells below the header I was able to narrow the count (when I used just the count the header was included thus my count was off. Now I have my answer and was able to create a Macro with concatenate that gives me the number and identifier (## customers). Loving this forum!

 

[Peter_SSs]

Glad that you have got what you want. Thanks for letting us know.