+ Reply to Thread
Results 1 to 14 of 14

If formula for more that 300 targets

  1. #1
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    If formula for more that 300 targets

    HI all,

    i have an IF formula that reads =if(a32="u",h32,0)+if(a33="u",h33,0)+ for another 300 lines......

    is there a quicker way to make this happen rather that manually do each line?

    please advise

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: If formula for more that 300 targets

    Try Sumif

    =SUMIF(A32:H332,"u",H32:H332)

  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: If formula for more that 300 targets

    =SUMIF(A32:A332,"u",H32:H332)

  4. #4
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: If formula for more that 300 targets

    sorry guys - i need to be way more specific. cells L363:T363 and L362:T362 are all if formulas based on column F and G AND the socring in the green box beside it. the vlookp and sum if wont work becuase there are header rows with the "u" or the "na" in them and they cant be counted.

    please see the attachment to make it all make sense...
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: If formula for more that 300 targets

    so you want to sum everything without a u? What are you actually trying to do?

  6. #6
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: If formula for more that 300 targets

    what it is doing at the bottom of the "green" section - is totaling the amount of points that are associated with a U or an NA. what i have done right now is manually choose which cells to look at the if statement. however i need to change this to allow for changes in the future as to where scoring is allocated

  7. #7
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: If formula for more that 300 targets

    So what defines a point that is associated with a U or an NA.

    From looking at the attached sheet, the total in L361 is summing everything above it apart from L316, L351 and L326.

    So if you summed everything apart from the rows which held the "10. Breakfast", "10. Breakfast - Total" and "11. Purblendz - Total" rows would that do?

    Maybe in L361:
    =SUM(L25:L360)-VLOOKUP("10. BREAKFAST - TOTAL", B25:L360,11,FALSE)-VLOOKUP("10. BREAKFAST",B25:L360,11,FALSE)-VLOOKUP("11. PURBLENDZ - TOTAL",B25:L360,11,FALSE)

    ? It returns the same as what you have at the moment..

  8. #8
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: If formula for more that 300 targets

    that gives a total count - not only the count based on column g having a "u" in it - thanks for your help!

  9. #9
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: If formula for more that 300 targets

    try putting a U in a few cells in the G column - the numbers will adjust on L depending on what is linked with the if formula.

  10. #10
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: If formula for more that 300 targets

    the count should only happen if a NA or U is present in column F and G

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: If formula for more that 300 targets

    Put filter in Column-B and use colors in filter and select Yellow,black,Grey background cells and in Column-A mark those cells with some text so that based on that we can ignore those rows from the sumifs() formula


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  12. #12
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: If formula for more that 300 targets

    Hi
    L363 cell
    =SUMPRODUCT((LEN($A$25:$A$350)>4)*($G$25:$G$350="u")*L25:L350)

    Its only for "U" letter. Tested it in your file and gives correct numbers. If I am on right way you can modify it for "NA" too
    Appreciate the help? CLICK *

  13. #13
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    Re: If formula for more that 300 targets

    AZ_XL!! thank you!!! fantastic

  14. #14
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: If formula for more that 300 targets

    You are welcome. Thank you for feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need Help with formula showing Expiring Dates And targets that are met
    By al3xwhit3 in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 01-18-2013, 06:41 AM
  2. Targets
    By Vokean in forum Excel General
    Replies: 7
    Last Post: 09-16-2012, 08:53 PM
  3. Formula to evaluate performance vs targets
    By OZManager in forum Excel General
    Replies: 1
    Last Post: 07-09-2012, 07:05 AM
  4. Conditional Targets
    By serovajoe in forum Excel General
    Replies: 1
    Last Post: 11-24-2010, 04:17 PM
  5. Sum To Work Out Targets
    By Mikey2005 in forum Excel General
    Replies: 1
    Last Post: 02-23-2006, 04:40 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1