Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula to count if in seperate columns

    Maybe somebody can help me:
    Firstly i need to check which letter is in Column range A4:A200.
    and THEN, IF it is letter B or C or M or Y . sum numbers in column B for letter in column A.

    Like in attached you see example :
    it will be A4 and A8 "b" letter sum = 2

    After that when i will have usage sum of every letter i need to minus it from sum of "K" column same letter.


    Thanks

    Andrius
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Feb 2010
    Location
    Florida
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Andrius,

    If I understand what you are asking, I suggest using the sumif function. To sum all the "b" in column A it would look like this: =SUMIF(A4:A200,"b",B4:B200)

    You can use the same function to find the sum of K: =SUMIF(G4:G200,"b",K4:K200).

    If you want to nest the functions, it would look like this: =(SUMIF(A4:A200,"b",B4:B200))-(SUMIF(G4:G200,"b",K4:K200))

    Lastly, you can replace the b with a cell reference. If you are going to list the B, C, M, Y in separete cells to indicate which item you are calculating, then change the "b" to refer to the cell (eg N9).

    Looking at the formula you have in your spreadsheet, I'm not sure what you want to do with the "colors". As always you can use F4 or $ to lock the cell reference in the range.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't follow exactly what you want, but it seems you may want a SUMIF:
    =SUMIF($A$4:$A$10,"b",$B$4:$B$10)

    This sums the values in B4:B8 where A4:A8 is "b".

    Steve

  4. #4
    New Lounger
    Join Date
    Jun 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks mates thats what i am looking for.

  5. #5
    New Lounger
    Join Date
    Jun 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I found another problem.
    You can imagine.

    In column A is validation by list contains 17 different names.
    and these 17 names have matches.

    Like

    printer 1 - Hp
    Printer 2 - canon
    printer 3 - hp
    printer 4 - epson

    What i wan to do.
    If i choose in column A from drop box Printer 1 in column B it should show HP
    if printer 2 canon.

    I tried to do with IF(A2:A200 ="printer 1", HP, IF(A2:A200 ="printer 3", HP.....
    but it allows only 7 IF's

    is it another way to do this?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Create a lookup table and use Vlookup to lookup the "printer x" and then get the corresponding name.

    Steve

Posting Permissions

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