Combining COUNTIF and AND functions

D

david

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
 
B

Bob Phillips

=SUMPRODUCT(--(issued_by_range=A8),--(Period_all_range=1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
C

colin_b

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
 
B

Bob Phillips

=SUMPRODUCT(--(JOINED>=F273),--(JOINED<=F274),--(ISNUMBER(FIND("INT",JOINED)
)))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
R

RagDyeR

Try this:

=SUMPRODUCT((JOINED>=F273)*(JOINED<=F274)*(ISNUMBER(SEARCH("INT",JOINED))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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
 
C

colin_b

Hi Bob,
Thanks for responding but this solution that you provided does not wor
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 th
example above, column b contains dates. The user can select 2 date
from two list boxes at 2 cells.
I want to count the number of times the string appears between thos
dates.
So my approach is:
count in the range A:B, where range is >=Date1 AND <=Date2 AND contain
the string "*INT*"
Can you assist me with this?
Thanks in advance,
Coli
 
C

colin_b

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 :)
 
R

RagDyeR

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 !
----------------------------------------------------------------------------
-------------------

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 :)
 
D

Dav

It worked for me but you have specified the same name for your range o
dates and your string, I assume they are referencing the seperat
columns to work

Your date I also assume is a date and not a text string

Regards

Da
 
C

colin_b

Hi Folks,
Thanks to all of you for your assistance. I have not made my proble
clear... sorry.

I have a named area of cells (C2:C272 and D2:d272) called JOINED. Thi
contains two columns, one with dates (formatted to date) and one colum
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),
want to return how many strings containing "INT" (or "SYS" etc) ther
are in the JOINED columns between the chosen dates. I then use thi
result for a graph.

I hope this is clearer.

Thanks again for you help,

Colin:
 
R

RagDyeR

Try this,
With dates in Column C and strings in Column D:

=SUMPRODUCT((C2:C272>=F273)*(C2:C272<=F274)*(ISNUMBER(SEARCH("INT",D2:D272))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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:)
 
R

RagDyeR

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)*(ISNUMBER(SEARCH(F275,D2:D272)))
)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Try this,
With dates in Column C and strings in Column D:

=SUMPRODUCT((C2:C272>=F273)*(C2:C272<=F274)*(ISNUMBER(SEARCH("INT",D2:D272))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

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:)
 
D

david

Thanks everyone for all your help - I've got it working now

and Hello again Max

David
 
C

colin_b

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:) :) :)
 
R

RagDyeR

Thank you both for the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

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:) :) :)
 
M

Max

Glad you got it working, David.
Thanks for the "wave".
It was getting kinda lonely down there <g>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding the weighted average of text that have been given values 1
Sumproduct not working 5
Combining comments using different versions of Word 0
Excel Countif Function - Calculating Between Two Sets of Values 3
Needs help combining functions. 3
Combining three files when middle file has sections with endnotes 2
countif 1
Formulaic range with CountIf 6

Top