Results 1 to 7 of 7

Thread: Multiple criterias checking... is it possible using if(countifs())?

  1. #1

    Multiple criterias checking... is it possible using if(countifs())?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    What I am looking for is a method to check from a long list if from the same Number set, if the Category D has greater days than any other higher category (A, B, C). I have been tinkering with an if(Countifs()) equation, but I cannnot get it to work.

    So in the data set below I would like a fourth column that would say Yes/No. In the first dat set below it would be a Yes result as there is a D with more Days than a B.
    Number Cat Days
    CS140012 B 1562.51
    CS140012 B 471.96
    CS140012 C 1695.88
    CS140012 D 557.14
    CS140012 D 517.41
    CS140012 D 237.61
    CS140012 E 0.00
    CS140012 E 0.00

    In the next data set the result would be "no" as there are no D's
    Num Cat Days
    CD140014 B 3450.00
    CD140014 B 2315.71
    CD140014 B 1434.57
    CD140014 B 1376.97
    CD140014 B 1140.36
    CD140014 C 8070.00
    CD140014 C 4594.23
    CD140014 C 210.44
    CD140014 E 0.00
    CD140014 F 0.00

    In this next data set the answer would be no, as the D has less days than the C's.
    Num Cat Days
    CD140015 C 870.00
    CD140015 C 319.60
    CD140015 D 318.40
    CD140015 E 0.00
    CD140015 E 0.00


    Any suggestions?

  2. #2
    Try this array formula

    =IF(MAX(IF(B2:B9="D",C2:C9))>MIN(IF((B2:B9<>"D")*(C2:C9<>0),C2:C9)),"yes","no")

  3. #3
    Thanks for the attempt Bob,

    My complete list is over 10k lines of data so it is impossible to use set cell references. What I need in the end is to check the same number in the first column and if there is a "D" compare the number of days of the A/B/C and if the nmber of days of the D is larger than any of the A/B/C's, which is why I was toying with the countifs equation.

  4. #4
    I don't see how the number of lines affects it.

  5. #5
    Hey
    Can u give me it on Excel Please?
    tell me what exactly do you want?

    Hope for your reply

  6. #6
    Hi Bob,

    Your equation works for one unique set of column A, for example your reference to B2:B9 is only for CS140012 from column A. My real se of data contains over 10k lines of dat with several hundred unique sets of data in column A (I have suppled 3 examples). If I were to use your equation I would have to go to each set and create another equation for that set. Imagine my three data sets all together. For the second example I would need =IF(MAX(IF(B10:B19="D",C10:C19))>MIN(IF((B10:B19<>"D")*(C10:C19<>0),C10:C19)),"yes","no"). If I am going to do that I might as well just look at each data set manually. I can't use B2:B9998 and C2:C9998 as that would assume one data set and not the several hundred I have.

    I hope I explained this well enough.

  7. #7
    If it's all in blocks of 10 rows, you could have a formula that copies down okay. If the blocks are not that structured, you would need to know what constitutes the start of the next block, a way of determining that in a formula.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •