# If formula for more that 300 targets

1. ## 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?

2. ## Re: If formula for more that 300 targets

Try Sumif

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

3. ## Re: If formula for more that 300 targets

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

4. ## 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...

5. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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

12. ## 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

13. ## Re: If formula for more that 300 targets

AZ_XL!! thank you!!! fantastic

14. ## Re: If formula for more that 300 targets

You are welcome. Thank you for feedback

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

#### 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