Formula needed to determine number of quarters in year/country combination per customer

[Robert1311]

Hi there, I hope someone can help me out.....!

Please have a look at the attached file.
In a list of customer files I like to determine,
only for Y cells, how many Q cells can be found for a specific Y/country code combination.
Example:
How many Q can be found for customer 13001 and countrycode 6. Answer = 2.

Is there a formula that solve the problem?

Rgds, Robert

[Sam Capricci]

try this formula... =COUNTIFS(D:D,"Q",E:E,6,C:C,13001)

[joris moerings]

Hi,

Small addition to response to cover the Y condition mentioned by OP
try this in cell G11 and copy up or down according to need:

Please Login or Register  to view this content.

[shukla.ankur281190]

Try
G3=IF(D3="Y",COUNTIFS($C$3:C3,$C3,$D$3:D3,"Q",$E$3:E3,$E3),"") and drag down.

[Robert1311]

Hi Joris, this works fine! Thanks very much

[samba_ravi]

G3

Please Login or Register  to view this content.

Try the above formula and copy towards down

[Sam Capricci]

after rereading your request I wonder if this might be a better formula if your list is longer and includes periods where there is no "Y".
=IF(COUNTIFS(C:C,13001,D:D,"y")>0,COUNTIFS(C:C,13001,D:D,"q",E:E,6),"")

[joris moerings]

Hi Robert,

Your welcome, i really appreciate if you would click *Add Reputation and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if the solution is to your satisfaction.