Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
Hello – need help with a formula. I’ve tried searching, looked through about 30 threads but can’t find a match. I need to search a column for 3 different parameters (“RC”,”NC”,”RS”) and if it finds it in a cell, I need to go to specific cell in another column (same row) and read (“complete” or “not done”) then total all those findings into the formula cell. I try to learn this stuff on my own, but my brain is just too tired today... Thanks rdj -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
Hi
Try something like this: =IF(OR(A2="RC",A2="NC",A2="RS"),"Complete","Not done") You can then fill this formula down all of your rows. For a total use COUNTIF(): =COUNTIF(C2:C1000,"Completed") Andy. "redneck joe" wrote in message ... Hello - need help with a formula. I've tried searching, looked through about 30 threads but can't find a match. I need to search a column for 3 different parameters ("RC","NC","RS") and if it finds it in a cell, I need to go to specific cell in another column (same row) and read ("complete" or "not done") then total all those findings into the formula cell. I try to learn this stuff on my own, but my brain is just too tired today... Thanks rdj -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
Can I combine the two into one formula? I've got the countif part for the first parameter (NC,RC,RS) to pull. Then if it sees one of those it needs to then check another specific cell for the complete/not complete and sum those only all those. The complete/not complete The column containing the NC,RC,RS can also contain other (job) types, but I only want to count the three listed, either complete.not complete. Make sense? Hi Try something like this: =IF(OR(A2="RC",A2="NC",A2="RS"),"Complete","Not done") You can then fill this formula down all of your rows. For a total use COUNTIF(): =COUNTIF(C2:C1000,"Completed") Andy. "redneck joe" wrote in message ... Hello - need help with a formula. I've tried searching, looked through about 30 threads but can't find a match. I need to search a column for 3 different parameters ("RC","NC","RS") and if it finds it in a cell, I need to go to specific cell in another column (same row) and read ("complete" or "not done") then total all those findings into the formula cell. I try to learn this stuff on my own, but my brain is just too tired today... Thanks rdj -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=523676 -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
=COUNTIF(A1:A1000,"NC")+COUNTIF(A1:A1000,"RC")+COU NTIF(A1:A1000,"RS") But then you don't get to have the Complete Not-Completes filled in on the other column -- kraljb ------------------------------------------------------------------------ kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
Not sure if this is what you want or not: =SUMPRODUCT((A1:A5000={"RC","NC","RS"})*(B1:B5000= "complete")) This gives you the number of times the word "complete" appears in column B and the letters RC, NC or RS appear in Col A on the same row. Is that what you're looking for? -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
not quite - I tried it and got an N/A. I'm sure I'm not explaining it correctly. in your formula, the A column individual jobs with corresponding job types (of which there are many) I am only concerned with the three listed. Then if it finds one in column A, it needs to look over to column B and read the complete/not complete for this indiviual job. maybe this will help? If job type in column A is X, then read cell X in same row, column B, then if "complete", sum all "completes" of job type X. ?? Cutter Wrote: Not sure if this is what you want or not: =SUMPRODUCT((A1:A5000={"RC","NC","RS"})*(B1:B5000= "complete")) This gives you the number of times the word "complete" appears in column B and the letters RC, NC or RS appear in Col A on the same row. Is that what you're looking for? -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
That is what the formula I gave you does. It looks at column A and if the value found is either RC, NC or RS it looks at column B. If it finds the word "complete" in column B it counts it. The end result is the total number of times RC and "complete" are on the same row + NC and "complete" are on the same row + RS and "complete" are on the same row. If that isn't what you want then I don't understand what you're looking for. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
I'm sure I didn't so it right - i'll try again. thanks Cutter Wrote: That is what the formula I gave you does. It looks at column A and if the value found is either RC, NC or RS it looks at column B. If it finds the word "complete" in column B it counts it. The end result is the total number of times RC and "complete" are on the same row + NC and "complete" are on the same row + RS and "complete" are on the same row. If that isn't what you want then I don't understand what you're looking for. -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
I've been staring at this dang thing too long. Note to self: When testing a formula, enter in the value you are testing for..... Thanks for your help - sorry to be a bit slow. -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
Does that mean it's working the way you need it to work (formula is giving results that match the expected results)? If so, you're welcome. Glad to help. If not, we'll keep trying. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
Yes, works perfectly.... I used to think I was good with excel, until I learned what it can really do - problem is every time I learn something new I feel more stupid than when I started. Been working on this one for about a week now - and alot of "eyes" will be looking at this one - so I've stressed to the point where I forget the obvious. One more question, different task. Is there an easy way to replicate formulas when the rows pull from different tabs? I'm making a summary page, and have 20 shops with their own tabs. A particular row needs info pulled from each sites' tab. Other than typing each formula, can you copy paste and have it adjust to the tab the way a formula does for cells? Cutter Wrote: Does that mean it's working the way you need it to work (formula is giving results that match the expected results)? If so, you're welcome. Glad to help. If not, we'll keep trying. -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
Firstly, glad you got it working. I, too, thought I was good at Excel. But it turns out that I only looked good because everyone else where I worked really sucked at it. Then I found this forum and discovered that I really suck at it compared to the guys and gals who are actually really good at it. Everything's relative. As for your new question. It sounds like you might be able to use the INDIRECT() function. I visualize your summary sheet as having the 20 shop names on a header row (one shop name to a column). If the shop names in that row match the shop names on the tabs you could utilize the INDIRECT() function to drag the formula across those columns. -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif help, please
Same story here. I'll try that one - thanks. I start with Help, research what I can, try over and over and over, then last ditch, ask for help. -- redneck joe ------------------------------------------------------------------------ redneck joe's Profile: http://www.excelforum.com/member.php...o&userid=32570 View this thread: http://www.excelforum.com/showthread...hreadid=523676 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
COUNTIF with Logic? | Excel Worksheet Functions | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions |