I hope someone can help me. I devised a s/sheet in Excel 2007 using the
COUNTIFS function. However, not all my colleagues like 2007 and have
reverted back to 2003. I have tried to redesign the formula using
various variations of COUNTIF but I can't get it to return the correct
value. The formula I use in Excel 2007 is:

=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)

Basically if the range within column D=Successful and the range within
column P=2007, I want it to count them.

Should I be using something else i.e. SUMPRODUCT?

Urgent help much appreciated.


--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile: http://www.thecodecage.com/forumz/member.php?userid=429
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107028

=SUMPRODUCT(--(D2:D123=SUCCESSFUL),--(P2:P123=2007),L2:L123)
or, if the word Successful is text and not a Named cell
=SUMPRODUCT(--(D2:D123="SUCCESSFUL"),--(P2:P123=2007),L2:L123)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"yorkshirelass" wrote in message
...

I hope someone can help me. I devised a s/sheet in Excel 2007 using the
COUNTIFS function. However, not all my colleagues like 2007 and have
reverted back to 2003. I have tried to redesign the formula using
various variations of COUNTIF but I can't get it to return the correct
value. The formula I use in Excel 2007 is:

=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)

Basically if the range within column D=Successful and the range within
column P=2007, I want it to count them.

Should I be using something else i.e. SUMPRODUCT?

Urgent help much appreciated.


--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile:
http://www.thecodecage.com/forumz/member.php?userid=429
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=107028

THIS DIDN'T WORK I'M AFRAID - IT RETURNED A RIDICULOUS NUMBER. THERE
ARE 15 MATCHES OF 'SUCCESSFUL' AND '2007' - ANY MORE IDEAS? I'VE LOOKED
AT THE LINKS YOU ATTACHED BUT CAN'T FIND ANYTHING THAT TIES IN WITH WHAT
I'M TRYING TO ACHIEVE


Bernard Liengme;382702 Wrote:
=SUMPRODUCT(--(D2:D123=SUCCESSFUL),--(P2:P123=2007),L2:L123)
or, if the word Successful is text and not a Named cell
=SUMPRODUCT(--(D2:D123="SUCCESSFUL"),--(P2:P123=2007),L2:L123)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
'McGimpsey & Associates : Excel : Formulae : Why \"--\"'
(http://mcgimpsey.com/excel/formulae/doubleneg.html)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' (http://people.stfx.ca/bliengme)
remove caps from email




"yorkshirelass" wrote in
message
...

I hope someone can help me. I devised a s/sheet in Excel 2007 using
the
COUNTIFS function. However, not all my colleagues like 2007 and
have
reverted back to 2003. I have tried to redesign the formula using
various variations of COUNTIF but I can't get it to return the
correct
value. The formula I use in Excel 2007 is:

=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)

Basically if the range within column D=Successful and the range
within
column P=2007, I want it to count them.

Should I be using something else i.e. SUMPRODUCT?

Urgent help much appreciated.


--
yorkshirelass

------------------------------------------------------------------------
yorkshirelass's Profile:
'The Code Cage Forums - View Profile: yorkshirelass'
(http://www.thecodecage.com/forumz/member.php?userid=429)
View this thread:
'COUNTIF function for multiple criteria - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=107028)



--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile: http://www.thecodecage.com/forumz/member.php?userid=429
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107028

You want

=SUMPRODUCT(--(L2:L123=SUCCESSFUL),--(P2:P123=2007))

or

=SUMPRODUCT(--(L2:L123="SUCCESSFUL"),--(P2:P123=2007))

depending on whether SUCCESSFUL is a cell or text

--
__________________________________
HTH

Bob

"yorkshirelass" wrote in message
...

THIS DIDN'T WORK I'M AFRAID - IT RETURNED A RIDICULOUS NUMBER. THERE
ARE 15 MATCHES OF 'SUCCESSFUL' AND '2007' - ANY MORE IDEAS? I'VE LOOKED
AT THE LINKS YOU ATTACHED BUT CAN'T FIND ANYTHING THAT TIES IN WITH WHAT
I'M TRYING TO ACHIEVE


Bernard Liengme;382702 Wrote:
=SUMPRODUCT(--(D2:D123=SUCCESSFUL),--(P2:P123=2007),L2:L123)
or, if the word Successful is text and not a Named cell
=SUMPRODUCT(--(D2:D123="SUCCESSFUL"),--(P2:P123=2007),L2:L123)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
'McGimpsey & Associates : Excel : Formulae : Why \"--\"'
(http://mcgimpsey.com/excel/formulae/doubleneg.html)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' (http://people.stfx.ca/bliengme)
remove caps from email




"yorkshirelass" wrote in
message
...

I hope someone can help me. I devised a s/sheet in Excel 2007 using
the
COUNTIFS function. However, not all my colleagues like 2007 and
have
reverted back to 2003. I have tried to redesign the formula using
various variations of COUNTIF but I can't get it to return the
correct
value. The formula I use in Excel 2007 is:

=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)

Basically if the range within column D=Successful and the range
within
column P=2007, I want it to count them.

Should I be using something else i.e. SUMPRODUCT?

Urgent help much appreciated.


--
yorkshirelass

------------------------------------------------------------------------
yorkshirelass's Profile:
'The Code Cage Forums - View Profile: yorkshirelass'
(http://www.thecodecage.com/forumz/member.php?userid=429)
View this thread:
'COUNTIF function for multiple criteria - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=107028)



--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile:
http://www.thecodecage.com/forumz/member.php?userid=429
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=107028

Hi,

First, your original formula
=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)
does not do what you state. L2:L123 is the first criteria range and then
you are setting the criteria for that to D2:D123??? Then the second critera
range is SUCCESSFUL (must be a range name to be entered this way) and the
second condition is P2:P123, and so on. Using the L2:L123 range suggests you
are treating COUNTIFS like SUMIFS and they are not structured the same.

Let's suppose you are trying to count the number of items that contain the
word "Successful" in the range D2:D123 and the number 2007 in column P2:P123
2007:
=COUNTIFS(D2:D123,"SUCCESSFUL",P2:P123,2007)
2003:
=SUMPRODUCT(--(D2:D123="Successful"),--(P2:P123=2007))

Note that these formulas will fail if the range P2:P123 are dates and you
are trying to test for the year 2007. In that case you should replace
P2:P123 in the last formula with --(YEAR(P2:P123)=2007)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"yorkshirelass" wrote:


THIS DIDN'T WORK I'M AFRAID - IT RETURNED A RIDICULOUS NUMBER. THERE
ARE 15 MATCHES OF 'SUCCESSFUL' AND '2007' - ANY MORE IDEAS? I'VE LOOKED
AT THE LINKS YOU ATTACHED BUT CAN'T FIND ANYTHING THAT TIES IN WITH WHAT
I'M TRYING TO ACHIEVE


Bernard Liengme;382702 Wrote:
=SUMPRODUCT(--(D2:D123=SUCCESSFUL),--(P2:P123=2007),L2:L123)
or, if the word Successful is text and not a Named cell
=SUMPRODUCT(--(D2:D123="SUCCESSFUL"),--(P2:P123=2007),L2:L123)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
'McGimpsey & Associates : Excel : Formulae : Why \"--\"'
(http://mcgimpsey.com/excel/formulae/doubleneg.html)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' (http://people.stfx.ca/bliengme)
remove caps from email




"yorkshirelass" wrote in
message
...

I hope someone can help me. I devised a s/sheet in Excel 2007 using
the
COUNTIFS function. However, not all my colleagues like 2007 and
have
reverted back to 2003. I have tried to redesign the formula using
various variations of COUNTIF but I can't get it to return the
correct
value. The formula I use in Excel 2007 is:

=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)

Basically if the range within column D=Successful and the range
within
column P=2007, I want it to count them.

Should I be using something else i.e. SUMPRODUCT?

Urgent help much appreciated.


--
yorkshirelass

------------------------------------------------------------------------
yorkshirelass's Profile:
'The Code Cage Forums - View Profile: yorkshirelass'
(http://www.thecodecage.com/forumz/member.php?userid=429)
View this thread:
'COUNTIF function for multiple criteria - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=107028)



--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile: http://www.thecodecage.com/forumz/member.php?userid=429
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107028

This won't work either - basically within the range D2:D123 are a range
of various outcomes of applications so there is either Awaiting
Decision, Successful and Unsuccessful - no other option. Within the
range P2:P123 is either the year either 2007 or 2008. Within the range
L2:L123 is the monetary value of the application. I want to count how
many applications have the following returns: Successful and 2007. I
will then be adapting this formula within a different cell to identify
Successful and 2008. In addition to this, I will will be running a
formula to display (in a different cell) the total amount of money for
successful applications within 2007 and 2008.

This worked within Excel 2007 using:
COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007) and SUMIFS for the
monetary worth.

Does this give more information to what I need. I really appreciate
your help.

Thanks
Liz


Bob Phillips;383017 Wrote:
You want

=SUMPRODUCT(--(L2:L123=SUCCESSFUL),--(P2:P123=2007))

or

=SUMPRODUCT(--(L2:L123="SUCCESSFUL"),--(P2:P123=2007))

depending on whether SUCCESSFUL is a cell or text

--
__________________________________
HTH

Bob

"yorkshirelass" wrote in
message
...

THIS DIDN'T WORK I'M AFRAID - IT RETURNED A RIDICULOUS NUMBER.
THERE
ARE 15 MATCHES OF 'SUCCESSFUL' AND '2007' - ANY MORE IDEAS? I'VE
LOOKED
AT THE LINKS YOU ATTACHED BUT CAN'T FIND ANYTHING THAT TIES IN WITH
WHAT
I'M TRYING TO ACHIEVE


Bernard Liengme;382702 Wrote:
=SUMPRODUCT(--(D2:D123=SUCCESSFUL),--(P2:P123=2007),L2:L123)
or, if the word Successful is text and not a Named cell
=SUMPRODUCT(--(D2:D123="SUCCESSFUL"),--(P2:P123=2007),L2:L123)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
'McGimpsey & Associates : Excel : Formulae : Why \"--\"'
('McGimpsey & Associates : Excel : Formulae : Why \"--\"'
(http://mcgimpsey.com/excel/formulae/doubleneg.html))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' ('Bernard Liengme'
(http://people.stfx.ca/bliengme))
remove caps from email




"yorkshirelass" wrote in
message
...

I hope someone can help me. I devised a s/sheet in Excel 2007
using
the
COUNTIFS function. However, not all my colleagues like 2007 and
have
reverted back to 2003. I have tried to redesign the formula
using
various variations of COUNTIF but I can't get it to return the
correct
value. The formula I use in Excel 2007 is:

=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)

Basically if the range within column D=Successful and the range
within
column P=2007, I want it to count them.

Should I be using something else i.e. SUMPRODUCT?

Urgent help much appreciated.


--
yorkshirelass


------------------------------------------------------------------------
yorkshirelass's Profile:
'The Code Cage Forums - View Profile: yorkshirelass'
('The Code Cage Forums - View Profile: yorkshirelass'
(http://www.thecodecage.com/forumz/me...hp?userid=429))
View this thread:
'COUNTIF function for multiple criteria - The Code Cage Forums'
('COUNTIF function for multiple criteria - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=107028))



--
yorkshirelass

------------------------------------------------------------------------
yorkshirelass's Profile:
'The Code Cage Forums - View Profile: yorkshirelass'
(http://www.thecodecage.com/forumz/member.php?userid=429)
View this thread:
'COUNTIF function for multiple criteria - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=107028)



--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile: http://www.thecodecage.com/forumz/member.php?userid=429
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107028

Then I think Bernard gave you what you want, even though you used COUNTIFS
when you meant SUMIFS, and your COUNTIFS doesn't work. Other than that, I am
lost.

--
__________________________________
HTH

Bob

"yorkshirelass" wrote in message
...

This won't work either - basically within the range D2:D123 are a range
of various outcomes of applications so there is either Awaiting
Decision, Successful and Unsuccessful - no other option. Within the
range P2:P123 is either the year either 2007 or 2008. Within the range
L2:L123 is the monetary value of the application. I want to count how
many applications have the following returns: Successful and 2007. I
will then be adapting this formula within a different cell to identify
Successful and 2008. In addition to this, I will will be running a
formula to display (in a different cell) the total amount of money for
successful applications within 2007 and 2008.

This worked within Excel 2007 using:
COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007) and SUMIFS for the
monetary worth.

Does this give more information to what I need. I really appreciate
your help.

Thanks
Liz


Bob Phillips;383017 Wrote:
You want

=SUMPRODUCT(--(L2:L123=SUCCESSFUL),--(P2:P123=2007))

or

=SUMPRODUCT(--(L2:L123="SUCCESSFUL"),--(P2:P123=2007))

depending on whether SUCCESSFUL is a cell or text

--
__________________________________
HTH

Bob

"yorkshirelass" wrote in
message
...

THIS DIDN'T WORK I'M AFRAID - IT RETURNED A RIDICULOUS NUMBER.
THERE
ARE 15 MATCHES OF 'SUCCESSFUL' AND '2007' - ANY MORE IDEAS? I'VE
LOOKED
AT THE LINKS YOU ATTACHED BUT CAN'T FIND ANYTHING THAT TIES IN WITH
WHAT
I'M TRYING TO ACHIEVE


Bernard Liengme;382702 Wrote:
=SUMPRODUCT(--(D2:D123=SUCCESSFUL),--(P2:P123=2007),L2:L123)
or, if the word Successful is text and not a Named cell
=SUMPRODUCT(--(D2:D123="SUCCESSFUL"),--(P2:P123=2007),L2:L123)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
'McGimpsey & Associates : Excel : Formulae : Why \"--\"'
('McGimpsey & Associates : Excel : Formulae : Why \"--\"'
(http://mcgimpsey.com/excel/formulae/doubleneg.html))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' ('Bernard Liengme'
(http://people.stfx.ca/bliengme))
remove caps from email




"yorkshirelass" wrote in
message
...

I hope someone can help me. I devised a s/sheet in Excel 2007
using
the
COUNTIFS function. However, not all my colleagues like 2007 and
have
reverted back to 2003. I have tried to redesign the formula
using
various variations of COUNTIF but I can't get it to return the
correct
value. The formula I use in Excel 2007 is:

=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)

Basically if the range within column D=Successful and the range
within
column P=2007, I want it to count them.

Should I be using something else i.e. SUMPRODUCT?

Urgent help much appreciated.


--
yorkshirelass


------------------------------------------------------------------------
yorkshirelass's Profile:
'The Code Cage Forums - View Profile: yorkshirelass'
('The Code Cage Forums - View Profile: yorkshirelass'
(http://www.thecodecage.com/forumz/me...hp?userid=429))
View this thread:
'COUNTIF function for multiple criteria - The Code Cage Forums'
('COUNTIF function for multiple criteria - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=107028))



--
yorkshirelass

------------------------------------------------------------------------
yorkshirelass's Profile:
'The Code Cage Forums - View Profile: yorkshirelass'
(http://www.thecodecage.com/forumz/member.php?userid=429)
View this thread:
'COUNTIF function for multiple criteria - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=107028)



--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile:
http://www.thecodecage.com/forumz/member.php?userid=429
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=107028

This worked - thank you very much. My next problem is trying to sum all
the monetary values within range L2:L123 if the criteria below is
correct i.e. that D2:D123=successful and P2-P123=2007. This is where I
used the SUMIFS formula within Excel 2007

Liz


Shane Devenshire;383055 Wrote:
Hi,

First, your original formula
=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)
does not do what you state. L2:L123 is the first criteria range and
then
you are setting the criteria for that to D2:D123??? Then the second
critera
range is SUCCESSFUL (must be a range name to be entered this way) and
the
second condition is P2:P123, and so on. Using the L2:L123 range
suggests you
are treating COUNTIFS like SUMIFS and they are not structured the
same.

Let's suppose you are trying to count the number of items that contain
the
word "Successful" in the range D2:D123 and the number 2007 in column
P2:P123
2007:
=COUNTIFS(D2:D123,"SUCCESSFUL",P2:P123,2007)
2003:
=SUMPRODUCT(--(D2:D123="Successful"),--(P2:P123=2007))

Note that these formulas will fail if the range P2:P123 are dates and
you
are trying to test for the year 2007. In that case you should replace
P2:P123 in the last formula with --(YEAR(P2:P123)=2007)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"yorkshirelass" wrote:


THIS DIDN'T WORK I'M AFRAID - IT RETURNED A RIDICULOUS NUMBER.
THERE
ARE 15 MATCHES OF 'SUCCESSFUL' AND '2007' - ANY MORE IDEAS? I'VE
LOOKED
AT THE LINKS YOU ATTACHED BUT CAN'T FIND ANYTHING THAT TIES IN WITH
WHAT
I'M TRYING TO ACHIEVE


Bernard Liengme;382702 Wrote:
=SUMPRODUCT(--(D2:D123=SUCCESSFUL),--(P2:P123=2007),L2:L123)
or, if the word Successful is text and not a Named cell
=SUMPRODUCT(--(D2:D123="SUCCESSFUL"),--(P2:P123=2007),L2:L123)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
'McGimpsey & Associates : Excel : Formulae : Why \"--\"'
('McGimpsey & Associates : Excel : Formulae : Why \"--\"'
(http://mcgimpsey.com/excel/formulae/doubleneg.html))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
'Bernard Liengme' ('Bernard Liengme'
(http://people.stfx.ca/bliengme))
remove caps from email




"yorkshirelass" wrote in
message
...

I hope someone can help me. I devised a s/sheet in Excel 2007
using
the
COUNTIFS function. However, not all my colleagues like 2007 and
have
reverted back to 2003. I have tried to redesign the formula
using
various variations of COUNTIF but I can't get it to return the
correct
value. The formula I use in Excel 2007 is:

=COUNTIFS(L2:L123,D2:D123,SUCCESSFUL,P2:P123,2007)

Basically if the range within column D=Successful and the range
within
column P=2007, I want it to count them.

Should I be using something else i.e. SUMPRODUCT?

Urgent help much appreciated.


--
yorkshirelass


------------------------------------------------------------------------
yorkshirelass's Profile:
'The Code Cage Forums - View Profile: yorkshirelass'
('The Code Cage Forums - View Profile: yorkshirelass'
(http://www.thecodecage.com/forumz/me...hp?userid=429))
View this thread:
'COUNTIF function for multiple criteria - The Code Cage Forums'
('COUNTIF function for multiple criteria - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=107028))



--
yorkshirelass

------------------------------------------------------------------------
yorkshirelass's Profile: 'The Code Cage Forums - View Profile:
yorkshirelass'
(http://www.thecodecage.com/forumz/member.php?userid=429)
View this thread: 'COUNTIF function for multiple criteria - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=107028)




--
yorkshirelass
------------------------------------------------------------------------
yorkshirelass's Profile: http://www.thecodecage.com/forumz/member.php?userid=429
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=107028

Hello,

I suggest to use my UDF Pstat for this:
http://www.sulprobil.com/html/pstat.html

Regards,
Bernd