# Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

1. ## Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

This problem has likely been asked and answered but it's hard to word it correctly for a search to find.

I need a formula that does- If Column L on Sheet1 says "X", add(find the sum) of Column K on Sheet1.

Thank you!  Register To Reply

2. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

Try this:

=SUMIF(L:L,"X",K:K)

If this formula is on another sheet, you will have to add Sheet1! before the column references.  Register To Reply

3. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

SumIf, that makes sense.
It's not working for me though.... Any idea why?
Here is what I am using:
=SUMIF('Certified Tracker'!F:F,"Bloomfield",'Certified Tracker'!K:K)

I have plenty of data on Sheet1, 'Certified Tracker', that has Bloomfield in column F and numbers in column K, but the formula is returning '0'.  Register To Reply

4. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
6) Click on "Close this window"  Register To Reply

5. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

if Bloomfield is part of a text string or has a leading or trailing space then the =SUMIF('Certified Tracker'!F:F,"Bloomfield",'Certified Tracker'!K:K) will not work.
you might need to change it to =SUMIF('Certified Tracker'!F:F,"*Bloomfield*",'Certified Tracker'!K:K) to pick it up as part of a string.

AND, make sure your numbers in col K aren't text.  Register To Reply

6. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

See attached. On Sheet2, 'Blmfld', I want cell C12 to have a formula to add all the 'projected units' together for region 'Bloomfield'.

Thank you!  Register To Reply

7. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

The numbers in column K are being seen as Text.

To fix this, highlight column K > Data > Text to Columns > Finish.

Then the SUMIF formula should work.  Register To Reply

8. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

Fabulous thank you.
I also see Sambo Kid said this above, not sure if it was edited and I saw it too quickly or if I missed it but either way thank you both  Register To Reply

9. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

Hello again.

Changing my numbers to be stored as numbers instead of text worked on the Sample document I provided, but for some reason I cannot get it to work on my actual document. Please see attached, another Sample replicating the issue.

The numbers in row K are being formatted as numbers, so why isn't the formula in 'Blmfld Tier 2' cell C12 working?

Thank you!  Register To Reply

10. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

you still have text values in column K of the certified tracker.
change your formula in K to this... =IF(L35=1, 0,IF(L35=2,0,IF(L35=3,3,IF(L35=4,3,IF(L35=5,4,IF(L35=6,4,IF(L35=7,4,IF(L35=8,4,IF(L35=9,6,IF(L35=10,6,IF(L35>10,6))))))))))) by getting rid of the quotes around the numbers.  Register To Reply

11. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

actually, you could change your if formula to this to shorten it... =IF(OR(L42=1,L42=2),0,IF(OR(L42=3,L42=4),3,IF(OR(L42=5,L42=6,L42=7,L42=8),4,IF(OR(L42=9,L42>=10),6))))
there are likely other ways too like this... =LOOKUP(L42,{1,3,5,9},{0,3,4,6})  Register To Reply

12. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

AH! The quotes around the numbers are the problem! Also thanks so much for showing me that way to shorten this!! I figured there must be some way to do exactly that but didn't know how and typing it all out worked just fine, too.

Thank you!!!  Register To Reply

13. ## Re: Sum Help - If Column L on Sheet1 says "X", find the sum of Column K on Sheet1.

You're welcome! Glad I could help. You'll learn this stuff as you use excel more and refer to this site - I know I did. AND thank you for the rep!   Register To Reply

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