Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Can anyone help me with the format for combining COUNTIF and AND in a
single function - I can't seem to get the format right. What I want to do is COUNTIF range 'Issued_by' = A8 AND range 'Period_all' = 1 Thanks for any help David |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
=SUMPRODUCT(--(issued_by_range=A8),--(Period_all_range=1))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "david" wrote in message oups.com... Can anyone help me with the format for combining COUNTIF and AND in a single function - I can't seem to get the format right. What I want to do is COUNTIF range 'Issued_by' = A8 AND range 'Period_all' = 1 Thanks for any help David |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Try: =SUMPRODUCT((Issued_by=A8)*(Period_all=1))
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "david" wrote: Can anyone help me with the format for combining COUNTIF and AND in a single function - I can't seem to get the format right. What I want to do is COUNTIF range 'Issued_by' = A8 AND range 'Period_all' = 1 Thanks for any help David |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Hi David, I also had them same question. I tried your suggestion and it works for me, only I need 3 conditions. I have the following, but it is not working: =SUMPRODUCT(--(JOINED=F273),--(JOINED<=F274),--(JOINED="*INT*")) Any assistance would be much appreciated. Tahnks, Colin -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
=SUMPRODUCT(--(JOINED=F273),--(JOINED<=F274),--(ISNUMBER(FIND("INT",JOINED)
))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "colin_b" wrote in message ... Hi David, I also had them same question. I tried your suggestion and it works for me, only I need 3 conditions. I have the following, but it is not working: =SUMPRODUCT(--(JOINED=F273),--(JOINED<=F274),--(JOINED="*INT*")) Any assistance would be much appreciated. Tahnks, Colin -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Try this:
=SUMPRODUCT((JOINED=F273)*(JOINED<=F274)*(ISNUMBE R(SEARCH("INT",JOINED)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "colin_b" wrote in message ... Hi David, I also had them same question. I tried your suggestion and it works for me, only I need 3 conditions. I have the following, but it is not working: =SUMPRODUCT(--(JOINED=F273),--(JOINED<=F274),--(JOINED="*INT*")) Any assistance would be much appreciated. Tahnks, Colin -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Hi Bob, Thanks for responding but this solution that you provided does not work for me. I am counting for strings INT, e.g. SYS-INT-1496. What I have is two columns of data, column A contains strings like the example above, column b contains dates. The user can select 2 dates from two list boxes at 2 cells. I want to count the number of times the string appears between those dates. So my approach is: count in the range A:B, where range is =Date1 AND <=Date2 AND contains the string "*INT*" Can you assist me with this? Thanks in advance, Colin -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Hi RD, Thanks for your help. I tried this using ISNUMBER and ISTEXT but it would not return the correct result either. Maybe I should try a different approach. Thanks, COlin :) -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Your explanation to Bob concerns me.
Are you perhaps using *entire* column references in your named ranges (A:A - B:B)? You *can't* do that with SumProduct. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "colin_b" wrote in message ... Hi RD, Thanks for your help. I tried this using ISNUMBER and ISTEXT but it would not return the correct result either. Maybe I should try a different approach. Thanks, COlin :) -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
It worked for me but you have specified the same name for your range of dates and your string, I assume they are referencing the seperate columns to work Your date I also assume is a date and not a text string Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Hi Folks, Thanks to all of you for your assistance. I have not made my problem clear... sorry. I have a named area of cells (C2:C272 and D2:d272) called JOINED. This contains two columns, one with dates (formatted to date) and one column of strings. I have two list boxes. Both contain the date column information. These are located at F272 and F273. So, when a user selects the From date (F272) and the To date (F273), I want to return how many strings containing "INT" (or "SYS" etc) there are in the JOINED columns between the chosen dates. I then use this result for a graph. I hope this is clearer. Thanks again for you help, Colin:) -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Try this,
With dates in Column C and strings in Column D: =SUMPRODUCT((C2:C272=F273)*(C2:C272<=F274)*(ISNUM BER(SEARCH("INT",D2:D272)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "colin_b" wrote in message ... Hi Folks, Thanks to all of you for your assistance. I have not made my problem clear... sorry. I have a named area of cells (C2:C272 and D2:d272) called JOINED. This contains two columns, one with dates (formatted to date) and one column of strings. I have two list boxes. Both contain the date column information. These are located at F272 and F273. So, when a user selects the From date (F272) and the To date (F273), I want to return how many strings containing "INT" (or "SYS" etc) there are in the JOINED columns between the chosen dates. I then use this result for a graph. I hope this is clearer. Thanks again for you help, Colin:) -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Since you mentioned using additional criteria for the string searches, you
could also reference a cell to contain that variable criteria, so that the formula itself wouldn't have to be revised: =SUMPRODUCT((C2:C272=F273)*(C2:C272<=F274)*(ISNUM BER(SEARCH(F275,D2:D272))) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... Try this, With dates in Column C and strings in Column D: =SUMPRODUCT((C2:C272=F273)*(C2:C272<=F274)*(ISNUM BER(SEARCH("INT",D2:D272)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "colin_b" wrote in message ... Hi Folks, Thanks to all of you for your assistance. I have not made my problem clear... sorry. I have a named area of cells (C2:C272 and D2:d272) called JOINED. This contains two columns, one with dates (formatted to date) and one column of strings. I have two list boxes. Both contain the date column information. These are located at F272 and F273. So, when a user selects the From date (F272) and the To date (F273), I want to return how many strings containing "INT" (or "SYS" etc) there are in the JOINED columns between the chosen dates. I then use this result for a graph. I hope this is clearer. Thanks again for you help, Colin:) -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Thanks everyone for all your help - I've got it working now
and Hello again Max David RagDyeR wrote: Since you mentioned using additional criteria for the string searches, you could also reference a cell to contain that variable criteria, so that the formula itself wouldn't have to be revised: =SUMPRODUCT((C2:C272=F273)*(C2:C272<=F274)*(ISNUM BER(SEARCH(F275,D2:D272))) ) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RagDyeR" wrote in message ... Try this, With dates in Column C and strings in Column D: =SUMPRODUCT((C2:C272=F273)*(C2:C272<=F274)*(ISNUM BER(SEARCH("INT",D2:D272)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "colin_b" wrote in message ... Hi Folks, Thanks to all of you for your assistance. I have not made my problem clear... sorry. I have a named area of cells (C2:C272 and D2:d272) called JOINED. This contains two columns, one with dates (formatted to date) and one column of strings. I have two list boxes. Both contain the date column information. These are located at F272 and F273. So, when a user selects the From date (F272) and the To date (F273), I want to return how many strings containing "INT" (or "SYS" etc) there are in the JOINED columns between the chosen dates. I then use this result for a graph. I hope this is clearer. Thanks again for you help, Colin:) -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Hi Folks, Thanks very much...this code works for me =SUMPRODUCT((C2:C272=F273)*(C2:C272<=F274)*(ISNUM BER(SEARCH("INT",D2:D272)) This is exactly what I needed. Thanks RD!! Colin:) :) :) -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Thank you both for the feed-back.
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "colin_b" wrote in message ... Hi Folks, Thanks very much...this code works for me =SUMPRODUCT((C2:C272=F273)*(C2:C272<=F274)*(ISNUM BER(SEARCH("INT",D2:D272)) This is exactly what I needed. Thanks RD!! Colin:) :) :) -- colin_b ------------------------------------------------------------------------ colin_b's Profile: http://www.excelforum.com/member.php...o&userid=36106 View this thread: http://www.excelforum.com/showthread...hreadid=558799 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining COUNTIF and AND functions
Glad you got it working, David.
Thanks for the "wave". It was getting kinda lonely down there <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "david" wrote: Thanks everyone for all your help - I've got it working now and Hello again Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|