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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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