CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
Analystanand
Analystanand

Reputation: 35

In excel Counting all values based on condition in other column and only max 3 times can be counted

I Need a formula in excel formula or macro to count the no. of title From TITLE Col. based on condition in col PSA Division but the condition is that the same image title can be counted only 3 times(more than 3 must be ignored)

For example in below sheet. If condition is PSA division=S_PIDM, then Title "Conors Push" must be counted only 3 times not 4 times. In the below case the total title will be 5 title which should be counted..

     Formula should count Total 5 titles
     Conors Push-3  
     Splash and Dash-1         
     Wind Power -1
     Flippin Hell)            


      Title             PSA Division
      ------            ------------
    Conors Push -          *S_PIDM*  
    Conors Push            *S_PIDM*
    Conors Push            *S_PIDM*  
    Flippin Hell           S_PIDM  
    Narrowing the Gap      S_PIDC  
    The Perfect Storm      S_PIDC  
    Conors Push            *S_PIDM*  
    Splash and Dash        *S_PIDM*  
    Wind Power             *S_PIDM*

Edit-1:

Excel working sheet

Total Formula

Upvotes: 0

Views: 188

Answers (2)

Analystanand
Analystanand

Reputation: 35

As this problem was unique of its kind.

Question was like "In Main sheet column A, if I have an image which has been accepted more than three times, I have to ignore any further occurrences of this title. To give you an example - If I filter column J/PSA Division 'G_PIDC' then looking at column A/Title the image called 'Patience' is listed 7 times. Because I can only count it to a maximum of 3 times, 4 of these have to be excluded (that was what I meant by <=3). If we look for the image called 'Awakening' it is listed 3 times so all of these can be counted."

For solving this i have taken unique approach to first concatenated formula
=IF(J2="G_PIDC",A2&J2,"OTHER")concatenated two columns data to cut down condition thing and also showing their count. Count Formula=IF(O2="OTHER",0,IF(COUNTIF([concatenation],O2)>3,3,COUNTIF([concatenation],O2))) as harp S suggested.

Then to sum all the titles based on given condition in this sheet i have utilised formula=SUMPRODUCT(International!P2:P1000/COUNTIFS(International!O2:O1000, International!O2:O1000,International!P2:P1000,International!P2:P1000)) suggested by Jeeped.Total count

Upvotes: 0

Harp S
Harp S

Reputation: 1

If you phrase your problem as count each unique TITLE+PSA Division combination but limit the maximum to 3, then you can do this using the COUNTIF() formula with the unique combinations concatenated in a helper column like this.

The formula in C2 is =A2&B2

The formula in D2 is,

=IF(COUNTIF(C$2:C$10,C2)>3,3,COUNTIF(C$2:C$10,C2))

an alternative would be MOD()

    extract

Upvotes: 0

Related Questions