Announcement

Collapse
No announcement yet.

Need a little Excel help.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need a little Excel help.

    I have to use excel for something I am doing at work at the moment, pain in the arse but I have no choice.

    I count and sum up quite a lot with arrays at the moment.

    For example

    ={SUM((A1:A1000="B")*(B1:B1000="C")*(C1:C1000="D") )}

    The problem occurs though when I only want C1:C1000 to count unique values.

    I can count unique in one column s using

    ={SUM(N(FREQUENCY(A1:A1000,A1:A1000)>0))}

    but I can't see a way to link the two. Any of you guys have a suggestion, other then for me to do it another way?
    Last edited by dbdg; 13 October 2003, 06:56.


  • #2
    Does SUMPRODUCT() work?
    DM says: Crunch with Matrox Users@ClimatePrediction.net

    Comment


    • #3
      Not quite what I need, that sums the two arrays but I need to actual count the number of rows that fit criteria, first colum = B, second = C and then third = D, but I need to replace = D with = unique.

      I have a work around at the moment but it requires me to use a couple of formula, want to try and do it in one if possible without any vba.

      Comment


      • #4
        Thought about it some more, and think I understand what you are trying to do. You need a two extra columns. One with the array formula (in, for example cells D11000):


        =FREQUENCY(C1:C1000,C1:C1000)

        and another (in column E) that is

        =Dn*IF(Dn=1,1,0) (where n is the row number obviously).

        Then you can use your first formula on columns A, B and E. The problem I think was in mixing array formulas and "normal" ones.

        I can attach an example if the above is not clear.

        Gnep.
        DM says: Crunch with Matrox Users@ClimatePrediction.net

        Comment


        • #5
          Yeah if you could Gnep, I can't get the frequency part to work, my head has gone to mush.

          Got it now, frequency appears to only work on numbers.

          The last part enters into E a 1 for all unique values, here is a twist for you, how can I also include the duplicate values once in this column?
          Last edited by dbdg; 13 October 2003, 07:22.

          Comment


          • #6
            Here you go - save it and remove the .zip extension to use it. I've made the assumption that your column C contains numerical data... hope this is OK.

            Also when entering the array formula, highlight D11000 (B2:B14 in my sheet), enter the formula in the formula box, then hit "CTRL-SHIFT-ENTER" to get it in there.
            Attached Files
            DM says: Crunch with Matrox Users@ClimatePrediction.net

            Comment


            • #7
              To include the duplicate entries only on the first occurence in column C (working on my spreadsheet layout now), use this:

              =IF(B2<>0,A2,"")

              in C2 and copy down to C14.
              DM says: Crunch with Matrox Users@ClimatePrediction.net

              Comment


              • #8
                Oh, and this will work with non-numerical values now

                EDIT: No it won't
                DM says: Crunch with Matrox Users@ClimatePrediction.net

                Comment


                • #9
                  You are a diamond gnep.

                  Comment


                  • #10
                    No probs. Unfortunately I spend far too much of my working life in Excel.
                    DM says: Crunch with Matrox Users@ClimatePrediction.net

                    Comment

                    Working...
                    X