Close

Results 1 to 9 of 9

Thread: Excel help

  1. #1
    Banned BigBrand's Avatar
    Join Date
    Dec 2010
    Location
    UK.
    Posts
    5,150
    Thanks
    1,817
    Thanked:        2,061
    Karma Level
    0

    Default Excel help

    Morning guys,

    If I have a colum say i2 to i20, and each cell contains one of three possible data values, is there a way to have a KEY at the side of the sheet to display the volume of each value in each cell.

    e.g. colums I1 to I30 are written as :

    yes
    maybe
    no
    no
    no
    maybe
    yes
    yes
    yes
    maybe


    .. etc.

    I want a key to tell me how many YES', No's and Maybe's there are, which will also auto update when I change a value from one value to any of the other two.


    Any ideas boys?

  2. #2
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    Default Re: Excel help

    3 Cells with the following in

    =COUNTIF(i2:i20,"Yes")
    =COUNTIF(i2:i20,"No")
    =COUNTIF(i2:i20,"Maybe")

    Thanks to akimba

    BigBrand (21st April 2015) 


  3. #3
    Banned BigBrand's Avatar
    Join Date
    Dec 2010
    Location
    UK.
    Posts
    5,150
    Thanks
    1,817
    Thanked:        2,061
    Karma Level
    0

    Default Re: Excel help

    Perfect!

    Thanks very much. That made it a whole lot easier!

  4. #4
    Banned BigBrand's Avatar
    Join Date
    Dec 2010
    Location
    UK.
    Posts
    5,150
    Thanks
    1,817
    Thanked:        2,061
    Karma Level
    0

    Default Re: Excel help

    Morning,

    Similar question to the one above, now I'm comfortable with the use of the above formula, I'm looking to use another one, similar to the above.

    I'm needing one for value against a cell for a certain data type.

    e.g.

    I have say 5 different object, each object has a value, which is different. I want to make a "key" to show the combined value of each object.

    Red - £5
    Orange - £3
    Red - £7
    Blue - £2
    Green - £1
    Red - £3
    Red - £6
    Blue - £4
    Red - £8


    I want to present the total like

    Red = X
    Orange = X
    Blue = X

    as an accumulative total, any help please?

  5. #5
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    Default Re: Excel help

    =SUMPRODUCT(--($A$1:$A$10="Red"),$B$1:$B$10)
    =SUMPRODUCT(--($A$1:$A$10="Green"),$B$1:$B$10)
    =SUMPRODUCT(--($A$1:$A$10="Orange"),$B$1:$B$10)
    =SUMPRODUCT(--($A$1:$A$10="Blue"),$B$1:$B$10)

    Can make this more dynamic by using your KEY change the "Red" to the cell ref i.e. E2 or whatever

    Thanks to akimba

    BigBrand (28th April 2015) 


  6. #6
    Banned BigBrand's Avatar
    Join Date
    Dec 2010
    Location
    UK.
    Posts
    5,150
    Thanks
    1,817
    Thanked:        2,061
    Karma Level
    0

    Default Re: Excel help

    Thanks Akimba, obviously still very new to this.

    Can I confirm that say the colours were in D5 to D90 and the values were in F5 to F90 the formula would be:

    =SUMPRODUCT(--($D$5:$D$90="Red"),$F$5:$F$90)
    =SUMPRODUCT(--($D$5:$D$90="Blue"),$F$5:$F$90)
    =SUMPRODUCT(--($D$5:$D$90="Green"),$F$5:$F$90)

    Also, how can I get both these formula to work if the RESULT data is displayed don a different workbook, do I need to add something in to direct the data to check the cells in workbook A specifically?



  7. #7
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    Default Re: Excel help

    Yep
    I wouldn't say I was an expert in Excel (some would tho) but as a bit of advice you really don't want to start linking Excel workbooks together it can be pretty flakey :-S

  8. #8
    DF Super Moderator Over Carl's Avatar
    Join Date
    Apr 2006
    Location
    London
    Posts
    13,198
    Thanks
    4,345
    Thanked:        1,779
    Karma Level
    1321

    Default Re: Excel help

    Been a very long time since I actually used to piss around with excel but iirc, there was some kind of glitch with numbers not being pulled from other workbooks in some circumstances. Iirc I figured out a manual workaround (I think I incorporated the formula into a macro, and I had the macro auto refresh the relevant sheets before running the calcs)

    Sorry my experience of that issue was back with Office 2003 so I have no idea whether that is still relevant and I can't even remember what the glitch actually was, and whether the bodge I mentioned above is how I actually fixed it.

  9. #9
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    3,019
    Thanks
    1,152
    Thanked:        836
    Karma Level
    336

    Default Re: Excel help

    Yeah still glitches from time to time and refreshing does fix it but it doesn't make it seamless at all.

    Also the workbooks are referenced using a exact location so if you move/rename workbooks (and I know you will say you wont ever move them but it will happen) you start breaking links and when you have daisy chained 5 or 6 together(which anyone who links 2 together ultimately ends up doing) this is a ball ache.

    What is it you are trying to do, if need data transferred between spreadsheets I usually load it across from one to the other before reporting on it, this way you can have the location of the workbook in a cell on a settings page (for instance) where it is easy to change etc.

    Thanks to akimba

    Over Carl (28th April 2015) 


Similar Threads

  1. [REQUEST] Excel help
    By Fear345 in forum PC Software
    Replies: 7
    Last Post: 19th August 2017, 10:25 PM
  2. Excel Help
    By Conkers in forum PC Software
    Replies: 2
    Last Post: 9th February 2010, 09:57 AM
  3. Excel XP
    By bt2k1 in forum PC Problems
    Replies: 6
    Last Post: 4th August 2007, 10:26 AM
  4. MS Excel Help
    By ss30 in forum PC Software
    Replies: 2
    Last Post: 2nd January 2007, 12:10 PM

Social Networking Bookmarks

Posting Permissions

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