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!
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!
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.
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'.
I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.
To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"
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.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
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!
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.
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
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!
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.
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})
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!!!
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks