Countif between two dates based on another value
 

Hi,

I'm trying to count the number of occurances of a value between two dates
that I specify.

I've done this by using the following formula and it works:

=COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales &
WIP'!E:E,"="&Analysis!C2)

The dates are on sheet 'Analysis' in C1 and C2. The dates are in column E
on sheet 'Pre-Sales & WIP'.

Problem - Although this works I want it to only do the count when a value in
column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example).

I added an IF function to the beginning of my formula but this only told it
to perform the calculation if column B contained the word "WIP" anywhere. I
want it to count the values for every occurance of the word "WIP".

eg:

WIP - Between Correct Dates Specfied = Count
(another word) - Between or Not Between Dates Specified = No Count

I do hope this makes sense, any help appreciated!

Countif between two dates based on another value
 

You can only use COUNTIF (or SUMIF) if you have one condition. As you
have more, then try this:

=SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales &
WIP'!E1:E100<=Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP"))

Note also that with SUMPRODUCT you can not use full column references
(unless you have Excel 2007), so I have made these refer to 100 rows -
change if you have more. I've assumed start date in C1 and end date in
C2 in the Analysis sheet.

Hope this helps.

Pete

On Feb 22, 11:22*am, Bradley Searle <Bradley
wrote:
Hi,

I'm trying to count the number of occurances of a value between two dates
that I specify.

I've done this by using the following formula and it works:

=COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales &
WIP'!E:E,"="&Analysis!C2)

The dates are on sheet 'Analysis' in C1 and C2. *The dates are in column E
on sheet 'Pre-Sales & WIP'.

Problem - Although this works I want it to only do the count when a value in
column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example).

I added an IF function to the beginning of my formula but this only told it
to perform the calculation if column B contained the word "WIP" anywhere. *I
want it to count the values for every occurance of the word "WIP".

eg:

WIP - Between Correct Dates Specfied = Count
(another word) - Between or Not Between Dates Specified = No Count

I do hope this makes sense, any help appreciated!

Countif between two dates based on another value
 

=SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales &
WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP"))
--
David Biddulph

"Bradley Searle" <Bradley wrote in message
...
Hi,

I'm trying to count the number of occurances of a value between two dates
that I specify.

I've done this by using the following formula and it works:

=COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales &
WIP'!E:E,"="&Analysis!C2)

The dates are on sheet 'Analysis' in C1 and C2. The dates are in column E
on sheet 'Pre-Sales & WIP'.

Problem - Although this works I want it to only do the count when a value
in
column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example).

I added an IF function to the beginning of my formula but this only told
it
to perform the calculation if column B contained the word "WIP" anywhere.
I
want it to count the values for every occurance of the word "WIP".

eg:

WIP - Between Correct Dates Specfied = Count
(another word) - Between or Not Between Dates Specified = No Count

I do hope this makes sense, any help appreciated!

Countif between two dates based on another value
 

Pete / David - Thank you so much for the quick reply! Your solutions worked
perfectly. Very impressed!

"David Biddulph" wrote:

=SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales &
WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP"))
--
David Biddulph

"Bradley Searle" <Bradley wrote in message
...
Hi,

I'm trying to count the number of occurances of a value between two dates
that I specify.

I've done this by using the following formula and it works:

=COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales &
WIP'!E:E,"="&Analysis!C2)

The dates are on sheet 'Analysis' in C1 and C2. The dates are in column E
on sheet 'Pre-Sales & WIP'.

Problem - Although this works I want it to only do the count when a value
in
column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example).

I added an IF function to the beginning of my formula but this only told
it
to perform the calculation if column B contained the word "WIP" anywhere.
I
want it to count the values for every occurance of the word "WIP".

eg:

WIP - Between Correct Dates Specfied = Count
(another word) - Between or Not Between Dates Specified = No Count

I do hope this makes sense, any help appreciated!

Countif between two dates based on another value
 

Glad to have been able to help.
--
David Biddulph

"Bradley Searle" wrote in message
...
Pete / David - Thank you so much for the quick reply! Your solutions
worked
perfectly. Very impressed!

"David Biddulph" wrote:

=SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales &
WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP"))
--
David Biddulph

"Bradley Searle" <Bradley wrote in
message
...
Hi,

I'm trying to count the number of occurances of a value between two
dates
that I specify.

I've done this by using the following formula and it works:

=COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales &
WIP'!E:E,"="&Analysis!C2)

The dates are on sheet 'Analysis' in C1 and C2. The dates are in
column E
on sheet 'Pre-Sales & WIP'.

Problem - Although this works I want it to only do the count when a
value
in
column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example).

I added an IF function to the beginning of my formula but this only
told
it
to perform the calculation if column B contained the word "WIP"
anywhere.
I
want it to count the values for every occurance of the word "WIP".

eg:

WIP - Between Correct Dates Specfied = Count
(another word) - Between or Not Between Dates Specified = No Count

I do hope this makes sense, any help appreciated!

Countif between two dates based on another value
 

Thanks for feeding back. Slight difference between the two formulae -
mine includes the end date whereas David's doesn't.

Pete

On Feb 22, 12:15*pm, Bradley Searle
wrote:
Pete / David - Thank you so much for the quick reply! *Your solutions worked
perfectly. *Very impressed!



"David Biddulph" wrote:
=SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales &
WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP"))
--
David Biddulph

"Bradley Searle" <Bradley wrote in message
...
Hi,

I'm trying to count the number of occurances of a value between two dates
that I specify.

I've done this by using the following formula and it works:

=COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales &
WIP'!E:E,"="&Analysis!C2)

The dates are on sheet 'Analysis' in C1 and C2. *The dates are in column E
on sheet 'Pre-Sales & WIP'.

Problem - Although this works I want it to only do the count when a value
in
column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example).

I added an IF function to the beginning of my formula but this only told
it
to perform the calculation if column B contained the word "WIP" anywhere.
I
want it to count the values for every occurance of the word "WIP".

eg:

WIP - Between Correct Dates Specfied = Count
(another word) - Between or Not Between Dates Specified = No Count

I do hope this makes sense, any help appreciated!- Hide quoted text -

- Show quoted text -

Countif between two dates based on another value
 

Yes. I'd tried to make my formula equivalent (in the date part) to the OP's
original formula.
--
David Biddulph

"Pete_UK" wrote in message
...
Thanks for feeding back. Slight difference between the two formulae -
mine includes the end date whereas David's doesn't.

Pete

On Feb 22, 12:15 pm, Bradley Searle
wrote:
Pete / David - Thank you so much for the quick reply! Your solutions
worked
perfectly. Very impressed!



"David Biddulph" wrote:
=SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales &
WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP"))
--
David Biddulph

"Bradley Searle" <Bradley wrote in
message
...
Hi,

I'm trying to count the number of occurances of a value between two
dates
that I specify.

I've done this by using the following formula and it works:

=COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales &
WIP'!E:E,"="&Analysis!C2)

The dates are on sheet 'Analysis' in C1 and C2. The dates are in
column E
on sheet 'Pre-Sales & WIP'.

Problem - Although this works I want it to only do the count when a
value
in
column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example).

I added an IF function to the beginning of my formula but this only
told
it
to perform the calculation if column B contained the word "WIP"
anywhere.
I
want it to count the values for every occurance of the word "WIP".

eg:

WIP - Between Correct Dates Specfied = Count
(another word) - Between or Not Between Dates Specified = No Count

I do hope this makes sense, any help appreciated!- Hide quoted text -

- Show quoted text -

Countif between two dates based on another value
 

Understood, David - I was just pointing out to the OP that there was a
slight difference between the two formulae.

Pete

On Feb 22, 1:43*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Yes. *I'd tried to make my formula equivalent (in the date part) to the OP's
original formula.
--
David Biddulph

"Pete_UK" wrote in message

...
Thanks for feeding back. Slight difference between the two formulae -
mine includes the end date whereas David's doesn't.

Pete

On Feb 22, 12:15 pm, Bradley Searle



wrote:
Pete / David - Thank you so much for the quick reply! Your solutions
worked
perfectly. Very impressed!

"David Biddulph" wrote:
=SUMPRODUCT(('Pre-Sales & WIP'!E1:E100=Analysis!C1)*('Pre-Sales &
WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP"))
--
David Biddulph

"Bradley Searle" <Bradley wrote in
message
...
Hi,

I'm trying to count the number of occurances of a value between two
dates
that I specify.

I've done this by using the following formula and it works:

=COUNTIF('Pre-Sales & WIP'!E:E,"="&Analysis!C1)-COUNTIF('Pre-Sales &
WIP'!E:E,"="&Analysis!C2)

The dates are on sheet 'Analysis' in C1 and C2. The dates are in
column E
on sheet 'Pre-Sales & WIP'.

Problem - Although this works I want it to only do the count when a
value
in
column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example).

I added an IF function to the beginning of my formula but this only
told
it
to perform the calculation if column B contained the word "WIP"
anywhere.
I
want it to count the values for every occurance of the word "WIP".

eg:

WIP - Between Correct Dates Specfied = Count
(another word) - Between or Not Between Dates Specified = No Count

I do hope this makes sense, any help appreciated!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -