| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If 0, count various codes in a range on another sheet
I have this formula in N37 =SUM(G37:M37)/5
If the above result is 0, I want to count the number of times a specific code(PQ) is in a specific range on another worksheet, and in cell N38, I'd like the result to be, if PQ occurs 2 times in that range, 2 PQ. And if not 0, then nothing. Something like. If N370, countif(data!A1:A7*PQ*,"") So the result in the sample would be 2 PQ A s s pq pq s s s Thanks Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0, count various codes in a range on another sheet
N38: =IF(N370,COUNTIF(data!A1:A7,"*PQ*"),"")
HTH Bob "Steve" wrote in message ... I have this formula in N37 =SUM(G37:M37)/5 If the above result is 0, I want to count the number of times a specific code(PQ) is in a specific range on another worksheet, and in cell N38, I'd like the result to be, if PQ occurs 2 times in that range, 2 PQ. And if not 0, then nothing. Something like. If N370, countif(data!A1:A7*PQ*,"") So the result in the sample would be 2 PQ A s s pq pq s s s Thanks Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0, count various codes in a range on another sheet
Try this formula in N38:
=IF(AND(N370,COUNTIF(range,"PQ")=2),"2 PQ","") replace the word range with the actual range on the other sheet you'll be looking for PQ in. I've assumed PQ will be on its own in the cells where it exists. If you wanted to do the whol elot in cell N37: =IF(AND(SUM(G37:M37)/50,COUNTIF(range,"PQ")=2),"2 PQ","") Basil "Steve" wrote: I have this formula in N37 =SUM(G37:M37)/5 If the above result is 0, I want to count the number of times a specific code(PQ) is in a specific range on another worksheet, and in cell N38, I'd like the result to be, if PQ occurs 2 times in that range, 2 PQ. And if not 0, then nothing. Something like. If N370, countif(data!A1:A7*PQ*,"") So the result in the sample would be 2 PQ A s s pq pq s s s Thanks Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0, count various codes in a range on another sheet
I can't get it to work. One thing I see that I may not have been clear on, is
that the formula appears to be using 2 as an absolute. What I meant to indicate is that the number of times the PQ is in the specific range may be 1,2, 3 any number of times. So I want to count how many times it's in the range. Such as - 1 PQ, or 2 PQ, or 3 PQ, etc. Thanks again, "Basil" wrote: Try this formula in N38: =IF(AND(N370,COUNTIF(range,"PQ")=2),"2 PQ","") replace the word range with the actual range on the other sheet you'll be looking for PQ in. I've assumed PQ will be on its own in the cells where it exists. If you wanted to do the whol elot in cell N37: =IF(AND(SUM(G37:M37)/50,COUNTIF(range,"PQ")=2),"2 PQ","") Basil "Steve" wrote: I have this formula in N37 =SUM(G37:M37)/5 If the above result is 0, I want to count the number of times a specific code(PQ) is in a specific range on another worksheet, and in cell N38, I'd like the result to be, if PQ occurs 2 times in that range, 2 PQ. And if not 0, then nothing. Something like. If N370, countif(data!A1:A7*PQ*,"") So the result in the sample would be 2 PQ A s s pq pq s s s Thanks Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0, count various codes in a range on another sheet
Try:
=IF(AND(N370,COUNTIF(range,"PQ")0),COUNTIF(range ,"PQ")&"PQ","") or you can use a variant of what Bob entered with what is above if it still doesn't work. B "Steve" wrote: I can't get it to work. One thing I see that I may not have been clear on, is that the formula appears to be using 2 as an absolute. What I meant to indicate is that the number of times the PQ is in the specific range may be 1,2, 3 any number of times. So I want to count how many times it's in the range. Such as - 1 PQ, or 2 PQ, or 3 PQ, etc. Thanks again, "Basil" wrote: Try this formula in N38: =IF(AND(N370,COUNTIF(range,"PQ")=2),"2 PQ","") replace the word range with the actual range on the other sheet you'll be looking for PQ in. I've assumed PQ will be on its own in the cells where it exists. If you wanted to do the whol elot in cell N37: =IF(AND(SUM(G37:M37)/50,COUNTIF(range,"PQ")=2),"2 PQ","") Basil "Steve" wrote: I have this formula in N37 =SUM(G37:M37)/5 If the above result is 0, I want to count the number of times a specific code(PQ) is in a specific range on another worksheet, and in cell N38, I'd like the result to be, if PQ occurs 2 times in that range, 2 PQ. And if not 0, then nothing. Something like. If N370, countif(data!A1:A7*PQ*,"") So the result in the sample would be 2 PQ A s s pq pq s s s Thanks Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0, count various codes in a range on another sheet
I couldn't get this one to work, so I tried Bobs.
"Basil" wrote: Try: =IF(AND(N370,COUNTIF(range,"PQ")0),COUNTIF(range ,"PQ")&"PQ","") or you can use a variant of what Bob entered with what is above if it still doesn't work. B "Steve" wrote: I can't get it to work. One thing I see that I may not have been clear on, is that the formula appears to be using 2 as an absolute. What I meant to indicate is that the number of times the PQ is in the specific range may be 1,2, 3 any number of times. So I want to count how many times it's in the range. Such as - 1 PQ, or 2 PQ, or 3 PQ, etc. Thanks again, "Basil" wrote: Try this formula in N38: =IF(AND(N370,COUNTIF(range,"PQ")=2),"2 PQ","") replace the word range with the actual range on the other sheet you'll be looking for PQ in. I've assumed PQ will be on its own in the cells where it exists. If you wanted to do the whol elot in cell N37: =IF(AND(SUM(G37:M37)/50,COUNTIF(range,"PQ")=2),"2 PQ","") Basil "Steve" wrote: I have this formula in N37 =SUM(G37:M37)/5 If the above result is 0, I want to count the number of times a specific code(PQ) is in a specific range on another worksheet, and in cell N38, I'd like the result to be, if PQ occurs 2 times in that range, 2 PQ. And if not 0, then nothing. Something like. If N370, countif(data!A1:A7*PQ*,"") So the result in the sample would be 2 PQ A s s pq pq s s s Thanks Steve |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0, count various codes in a range on another sheet
This one worked to give me the # of PQ's. Thanks,
But I tried to add to it &text to get the # of PQ's & the text PQ, but I couldn't figure it out. I'd like the result to be 1 PQ Thanks again, Steve "Bob Phillips" wrote: N38: =IF(N370,COUNTIF(data!A1:A7,"*PQ*"),"") HTH Bob "Steve" wrote in message ... I have this formula in N37 =SUM(G37:M37)/5 If the above result is 0, I want to count the number of times a specific code(PQ) is in a specific range on another worksheet, and in cell N38, I'd like the result to be, if PQ occurs 2 times in that range, 2 PQ. And if not 0, then nothing. Something like. If N370, countif(data!A1:A7*PQ*,"") So the result in the sample would be 2 PQ A s s pq pq s s s Thanks Steve . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0, count various codes in a range on another sheet
=IF(N370,COUNTIF(data!A1:A7,"*PQ*"),"") & " PQ"
Or simply custom format to 0 " PQ" Gord Dibben MS Excel MVP On Fri, 8 Jan 2010 12:03:01 -0800, Steve wrote: This one worked to give me the # of PQ's. Thanks, But I tried to add to it &text to get the # of PQ's & the text PQ, but I couldn't figure it out. I'd like the result to be 1 PQ Thanks again, Steve "Bob Phillips" wrote: N38: =IF(N370,COUNTIF(data!A1:A7,"*PQ*"),"") HTH Bob "Steve" wrote in message ... I have this formula in N37 =SUM(G37:M37)/5 If the above result is 0, I want to count the number of times a specific code(PQ) is in a specific range on another worksheet, and in cell N38, I'd like the result to be, if PQ occurs 2 times in that range, 2 PQ. And if not 0, then nothing. Something like. If N370, countif(data!A1:A7*PQ*,"") So the result in the sample would be 2 PQ A s s pq pq s s s Thanks Steve . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If 0, count various codes in a range on another sheet
Pefect,
Thanks, Steve "Gord Dibben" wrote: =IF(N370,COUNTIF(data!A1:A7,"*PQ*"),"") & " PQ" Or simply custom format to 0 " PQ" Gord Dibben MS Excel MVP On Fri, 8 Jan 2010 12:03:01 -0800, Steve wrote: This one worked to give me the # of PQ's. Thanks, But I tried to add to it &text to get the # of PQ's & the text PQ, but I couldn't figure it out. I'd like the result to be 1 PQ Thanks again, Steve "Bob Phillips" wrote: N38: =IF(N370,COUNTIF(data!A1:A7,"*PQ*"),"") HTH Bob "Steve" wrote in message ... I have this formula in N37 =SUM(G37:M37)/5 If the above result is 0, I want to count the number of times a specific code(PQ) is in a specific range on another worksheet, and in cell N38, I'd like the result to be, if PQ occurs 2 times in that range, 2 PQ. And if not 0, then nothing. Something like. If N370, countif(data!A1:A7*PQ*,"") So the result in the sample would be 2 PQ A s s pq pq s s s Thanks Steve . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count occurences of same zip codes | Excel Worksheet Functions | |||
| Countif over a range of cells for various codes. | Excel Worksheet Functions | |||
| Looking up a value within a range of 3 letter or number codes | Excel Worksheet Functions | |||
| Looking up a value within a range of 3 letter or number codes... | Excel Worksheet Functions | |||
| How do I count alpha codes in a range of cells in a row? | Excel Discussion (Misc queries) | |||
Linear Mode
