If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Need help with Countif function
I broke it out, and I get results with the first one OK, but get 0 on the
seond one (but when editting the function, the formula displays a value). "Biff" wrote: Hi! Try breaking the formula into 2 formulas and see what you get: Formula to count just column A: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100)))) Formula to count just column B: =SUMPRODUCT(--(B1:B100="XXXX")) Do either of these return 0? Biff "Mary-Lou" wrote in message ... Thanks. I'm getting the same results when I tried the array Max included - 0 value displays within the cell, but when editting the function, the formula results display a value. The sentences within Column A can be really wonky and could be very long - can the funny results be because of the data within Column A? "Biff" wrote: Hi! Try this: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX")) Biff "Mary-Lou" wrote in message ... I need to count two different text values within two columns. Column A contains sentences and Column B contains various values. Example of small sentences in Column A: Custom XX Upgrade Printing reports on XX Custom YY Upgrade Example of values in Column B: AAAA BBBB BBBB What I need to do is count groups of records, like only counting the records that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B. I tried countif statements with no success. Any ideas? |
#12
|
|||
|
|||
Need help with Countif function
"Mary-Lou" wrote:
Hey, I did finally get it to work! I had copied the formula several times in my worksheet and D1 and E1 values had been adjusted in the copies - so once I put them back to what they should have been - BINGO! I will make them absolute so they don't change. Thanks very much. You're welcome ! Glad you finally got it up working over there g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#13
|
|||
|
|||
Need help with Countif function
I'm confused.........
You're reply to Max says you got it working. Is it working or not? The only difference, other than syntax, between mine and Max's formulas is the use of the TRIM function on the criteria cells. Biff "Mary-Lou" wrote in message ... I broke it out, and I get results with the first one OK, but get 0 on the seond one (but when editting the function, the formula displays a value). "Biff" wrote: Hi! Try breaking the formula into 2 formulas and see what you get: Formula to count just column A: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100)))) Formula to count just column B: =SUMPRODUCT(--(B1:B100="XXXX")) Do either of these return 0? Biff "Mary-Lou" wrote in message ... Thanks. I'm getting the same results when I tried the array Max included - 0 value displays within the cell, but when editting the function, the formula results display a value. The sentences within Column A can be really wonky and could be very long - can the funny results be because of the data within Column A? "Biff" wrote: Hi! Try this: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX")) Biff "Mary-Lou" wrote in message ... I need to count two different text values within two columns. Column A contains sentences and Column B contains various values. Example of small sentences in Column A: Custom XX Upgrade Printing reports on XX Custom YY Upgrade Example of values in Column B: AAAA BBBB BBBB What I need to do is count groups of records, like only counting the records that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B. I tried countif statements with no success. Any ideas? |
#14
|
|||
|
|||
Need help with Countif function
Hi. I was able to get Max's formula to work, but I am unable to get yours to
work. The two functions are different. I'm still working on getting your version up and running... it's bugging me now why it won't work. "Biff" wrote: I'm confused......... You're reply to Max says you got it working. Is it working or not? The only difference, other than syntax, between mine and Max's formulas is the use of the TRIM function on the criteria cells. Biff "Mary-Lou" wrote in message ... I broke it out, and I get results with the first one OK, but get 0 on the seond one (but when editting the function, the formula displays a value). "Biff" wrote: Hi! Try breaking the formula into 2 formulas and see what you get: Formula to count just column A: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100)))) Formula to count just column B: =SUMPRODUCT(--(B1:B100="XXXX")) Do either of these return 0? Biff "Mary-Lou" wrote in message ... Thanks. I'm getting the same results when I tried the array Max included - 0 value displays within the cell, but when editting the function, the formula results display a value. The sentences within Column A can be really wonky and could be very long - can the funny results be because of the data within Column A? "Biff" wrote: Hi! Try this: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX")) Biff "Mary-Lou" wrote in message ... I need to count two different text values within two columns. Column A contains sentences and Column B contains various values. Example of small sentences in Column A: Custom XX Upgrade Printing reports on XX Custom YY Upgrade Example of values in Column B: AAAA BBBB BBBB What I need to do is count groups of records, like only counting the records that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B. I tried countif statements with no success. Any ideas? |
#15
|
|||
|
|||
Need help with Countif function
I finally have the following 2 separate functions working but still working
on putting them together. Here's what I have: Part 1: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600)))) (looking for any row that contains "AM " within the column) Part 2: =SUMPRODUCT(--(H9:H600="channels")) Here's what I have as one formula - that still returns 0: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels")) "Mary-Lou" wrote: Hi. I was able to get Max's formula to work, but I am unable to get yours to work. The two functions are different. I'm still working on getting your version up and running... it's bugging me now why it won't work. "Biff" wrote: I'm confused......... You're reply to Max says you got it working. Is it working or not? The only difference, other than syntax, between mine and Max's formulas is the use of the TRIM function on the criteria cells. Biff "Mary-Lou" wrote in message ... I broke it out, and I get results with the first one OK, but get 0 on the seond one (but when editting the function, the formula displays a value). "Biff" wrote: Hi! Try breaking the formula into 2 formulas and see what you get: Formula to count just column A: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100)))) Formula to count just column B: =SUMPRODUCT(--(B1:B100="XXXX")) Do either of these return 0? Biff "Mary-Lou" wrote in message ... Thanks. I'm getting the same results when I tried the array Max included - 0 value displays within the cell, but when editting the function, the formula results display a value. The sentences within Column A can be really wonky and could be very long - can the funny results be because of the data within Column A? "Biff" wrote: Hi! Try this: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX")) Biff "Mary-Lou" wrote in message ... I need to count two different text values within two columns. Column A contains sentences and Column B contains various values. Example of small sentences in Column A: Custom XX Upgrade Printing reports on XX Custom YY Upgrade Example of values in Column B: AAAA BBBB BBBB What I need to do is count groups of records, like only counting the records that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B. I tried countif statements with no success. Any ideas? |
#16
|
|||
|
|||
Need help with Countif function
OK - I finally got it working. Apparently the functions are really picky
about where the rows start. I entered row 9 in the formula, but had inserted 2 extra blank rows in 9 and 10, moving the data down to row 11. I decided to re-enter the range into the formula and voila - it worked. Thanks for your help and patience. "Mary-Lou" wrote: I finally have the following 2 separate functions working but still working on putting them together. Here's what I have: Part 1: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600)))) (looking for any row that contains "AM " within the column) Part 2: =SUMPRODUCT(--(H9:H600="channels")) Here's what I have as one formula - that still returns 0: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels")) "Mary-Lou" wrote: Hi. I was able to get Max's formula to work, but I am unable to get yours to work. The two functions are different. I'm still working on getting your version up and running... it's bugging me now why it won't work. "Biff" wrote: I'm confused......... You're reply to Max says you got it working. Is it working or not? The only difference, other than syntax, between mine and Max's formulas is the use of the TRIM function on the criteria cells. Biff "Mary-Lou" wrote in message ... I broke it out, and I get results with the first one OK, but get 0 on the seond one (but when editting the function, the formula displays a value). "Biff" wrote: Hi! Try breaking the formula into 2 formulas and see what you get: Formula to count just column A: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100)))) Formula to count just column B: =SUMPRODUCT(--(B1:B100="XXXX")) Do either of these return 0? Biff "Mary-Lou" wrote in message ... Thanks. I'm getting the same results when I tried the array Max included - 0 value displays within the cell, but when editting the function, the formula results display a value. The sentences within Column A can be really wonky and could be very long - can the funny results be because of the data within Column A? "Biff" wrote: Hi! Try this: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX")) Biff "Mary-Lou" wrote in message ... I need to count two different text values within two columns. Column A contains sentences and Column B contains various values. Example of small sentences in Column A: Custom XX Upgrade Printing reports on XX Custom YY Upgrade Example of values in Column B: AAAA BBBB BBBB What I need to do is count groups of records, like only counting the records that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B. I tried countif statements with no success. Any ideas? |
#17
|
|||
|
|||
Need help with Countif function
The two functions are different
Upon closer inspection, yes, they are. In your sample data you say: Example of values in Column B: AAAA BBBB BBBB I assumed that those are representative values and my formula looks for a specific value "XXXX". Max's formula looks for a specific string: ISNUMBER(SEARCH(TRIM(E1),$B$1:$B$100)))) So, either your column B has values like: Custom XXXX Upgrade Printing reports on XXXX Custom YYYY Upgrade Or, if your column B values really a Example of values in Column B: AAAA BBBB BBBB Then you have unseen characters like leading and/or trailing spaces: spaceAAAA BBBBspace XXXXspace spaceXXXX Biff "Mary-Lou" wrote in message ... Hi. I was able to get Max's formula to work, but I am unable to get yours to work. The two functions are different. I'm still working on getting your version up and running... it's bugging me now why it won't work. "Biff" wrote: I'm confused......... You're reply to Max says you got it working. Is it working or not? The only difference, other than syntax, between mine and Max's formulas is the use of the TRIM function on the criteria cells. Biff "Mary-Lou" wrote in message ... I broke it out, and I get results with the first one OK, but get 0 on the seond one (but when editting the function, the formula displays a value). "Biff" wrote: Hi! Try breaking the formula into 2 formulas and see what you get: Formula to count just column A: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100)))) Formula to count just column B: =SUMPRODUCT(--(B1:B100="XXXX")) Do either of these return 0? Biff "Mary-Lou" wrote in message ... Thanks. I'm getting the same results when I tried the array Max included - 0 value displays within the cell, but when editting the function, the formula results display a value. The sentences within Column A can be really wonky and could be very long - can the funny results be because of the data within Column A? "Biff" wrote: Hi! Try this: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX")) Biff "Mary-Lou" wrote in message ... I need to count two different text values within two columns. Column A contains sentences and Column B contains various values. Example of small sentences in Column A: Custom XX Upgrade Printing reports on XX Custom YY Upgrade Example of values in Column B: AAAA BBBB BBBB What I need to do is count groups of records, like only counting the records that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B. I tried countif statements with no success. Any ideas? |
#18
|
|||
|
|||
Need help with Countif function
That doesn't look anything like your original post! BG
=SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels")) I assume that's an intentional space after the M: "*AM *" You don't need wildcards with SEARCH: =SUMPRODUCT(--(ISNUMBER(SEARCH("AM ",E9:E600))),--(H9:H600="channels")) Biff "Mary-Lou" wrote in message ... I finally have the following 2 separate functions working but still working on putting them together. Here's what I have: Part 1: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600)))) (looking for any row that contains "AM " within the column) Part 2: =SUMPRODUCT(--(H9:H600="channels")) Here's what I have as one formula - that still returns 0: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels")) "Mary-Lou" wrote: Hi. I was able to get Max's formula to work, but I am unable to get yours to work. The two functions are different. I'm still working on getting your version up and running... it's bugging me now why it won't work. "Biff" wrote: I'm confused......... You're reply to Max says you got it working. Is it working or not? The only difference, other than syntax, between mine and Max's formulas is the use of the TRIM function on the criteria cells. Biff "Mary-Lou" wrote in message ... I broke it out, and I get results with the first one OK, but get 0 on the seond one (but when editting the function, the formula displays a value). "Biff" wrote: Hi! Try breaking the formula into 2 formulas and see what you get: Formula to count just column A: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100)))) Formula to count just column B: =SUMPRODUCT(--(B1:B100="XXXX")) Do either of these return 0? Biff "Mary-Lou" wrote in message ... Thanks. I'm getting the same results when I tried the array Max included - 0 value displays within the cell, but when editting the function, the formula results display a value. The sentences within Column A can be really wonky and could be very long - can the funny results be because of the data within Column A? "Biff" wrote: Hi! Try this: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX")) Biff "Mary-Lou" wrote in message ... I need to count two different text values within two columns. Column A contains sentences and Column B contains various values. Example of small sentences in Column A: Custom XX Upgrade Printing reports on XX Custom YY Upgrade Example of values in Column B: AAAA BBBB BBBB What I need to do is count groups of records, like only counting the records that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B. I tried countif statements with no success. Any ideas? |
#19
|
|||
|
|||
Need help with Countif function
Ignore the heading...G I confirm that Max's function works for me: Try this: With the text in col A (range named text) and the codes in column B (range named codes), then create a matrix of possible combinations, starting in: D2 (and down), for text variables (eg XX) E1 (and across), for codes, eg AAAA Then in E2, type: =SUMPRODUCT(--(ISNUMBER(SEARCH($D2,text))),--(codes=E$1)) then copy this cell down the column range, then copy that range across the rest of the matrix. No array entry required. I'm hoping this will also give you the right answer, and many thanks to Max for the work. I overlooked using the wildcard in the search function, but it worked anyway. Regards Mike -- Mikeopolo ------------------------------------------------------------------------ Mikeopolo's Profile: http://www.excelforum.com/member.php...o&userid=18570 View this thread: http://www.excelforum.com/showthread...hreadid=547699 |
#20
|
|||
|
|||
Need help with Countif function
In my post, I was just giving a generic example. Without the wildcard
(including the space) I got too many hits, for example, just searching for "am" on it's own would pick up the letters embedded within words - but I only wanted them on their own. "Biff" wrote: That doesn't look anything like your original post! BG =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels")) I assume that's an intentional space after the M: "*AM *" You don't need wildcards with SEARCH: =SUMPRODUCT(--(ISNUMBER(SEARCH("AM ",E9:E600))),--(H9:H600="channels")) Biff "Mary-Lou" wrote in message ... I finally have the following 2 separate functions working but still working on putting them together. Here's what I have: Part 1: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600)))) (looking for any row that contains "AM " within the column) Part 2: =SUMPRODUCT(--(H9:H600="channels")) Here's what I have as one formula - that still returns 0: =SUMPRODUCT(--(ISNUMBER(SEARCH("*AM *",E9:E600))),--(H9:H600="channels")) "Mary-Lou" wrote: Hi. I was able to get Max's formula to work, but I am unable to get yours to work. The two functions are different. I'm still working on getting your version up and running... it's bugging me now why it won't work. "Biff" wrote: I'm confused......... You're reply to Max says you got it working. Is it working or not? The only difference, other than syntax, between mine and Max's formulas is the use of the TRIM function on the criteria cells. Biff "Mary-Lou" wrote in message ... I broke it out, and I get results with the first one OK, but get 0 on the seond one (but when editting the function, the formula displays a value). "Biff" wrote: Hi! Try breaking the formula into 2 formulas and see what you get: Formula to count just column A: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100)))) Formula to count just column B: =SUMPRODUCT(--(B1:B100="XXXX")) Do either of these return 0? Biff "Mary-Lou" wrote in message ... Thanks. I'm getting the same results when I tried the array Max included - 0 value displays within the cell, but when editting the function, the formula results display a value. The sentences within Column A can be really wonky and could be very long - can the funny results be because of the data within Column A? "Biff" wrote: Hi! Try this: =SUMPRODUCT(--(ISNUMBER(SEARCH("XX",A1:A100))),--(B1:B100="XXXX")) Biff "Mary-Lou" wrote in message ... I need to count two different text values within two columns. Column A contains sentences and Column B contains various values. Example of small sentences in Column A: Custom XX Upgrade Printing reports on XX Custom YY Upgrade Example of values in Column B: AAAA BBBB BBBB What I need to do is count groups of records, like only counting the records that contain "XX" anyplace witihin Column A plus have "XXXX" in Column B. I tried countif statements with no success. Any ideas? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
CountIf Function Help Needed | Mark | Worksheet Functions | 4 | January 30th, 2006 03:37 AM |
determine if Excel is open | geebee | General Discussion | 11 | January 20th, 2006 09:25 PM |
COUNTIF Subtotal Function? | jcpotwor | General Discussion | 2 | January 12th, 2006 01:56 PM |
Countif Function, complex criteria | Tomski | Worksheet Functions | 4 | January 9th, 2006 03:45 PM |
How do I use COUNTIF in a SUBTOTAL function to differentiate the . | Lettie | Worksheet Functions | 6 | March 22nd, 2005 08:47 AM |