Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default Need function that will work in Excel 2003 like "Countifs" in 2007

The following is an example of my database. I am looking for a formula that
will provide me a count of the number of times a "Rank" is less than 20
(<20) at the end of each row.

Stat Rank Stat Rank Stat Rank Stat Rank
20.82 48 19.18 86 24.65 6 23.20 16
23 62 12 6 18 31 17 24
28 18 24 37 29 14 25 28
0.33 38 0.80 24 0.79 25 0.57 31

The answer for row one should be "1"; row 2 should be "1"; row 3 should be
"0"; etc. I don't know if I'm being clear in the description of my needs.
At the end of each row I would like a formula that finds the heading of
"Rank" and evaluates the cell reference for that heading to determine whether
or not it is less than 20 (<20). If it is, then I would like to have it
included in the count, if not, then discard it.

Note: I was able to achieve this outcome when I used "Countifs" in Excel
2007, but now I only have access to Excel 2003 and the "Countifs" formula is
not compatible with this older version.

Thanks for your assistance.

--
RD
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Need function that will work in Excel 2003 like "Countifs" in 2007

The answers you gave for your examples are wrong!

If you're using 8 columns, your returns for the 4 rows should be:

2, 1, 2, 0

With data entered in A1 to H5, with headers in Row 1,
This formula entered in I2, and copied down, will give the returns I
mentioned:

=SUMPRODUCT((A$1:H$1="Rank")*(A2:H2<20))
--

HTH,

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



"RD" wrote in message
...
The following is an example of my database. I am looking for a formula that
will provide me a count of the number of times a "Rank" is less than 20
(<20) at the end of each row.

Stat Rank Stat Rank Stat Rank Stat Rank
20.82 48 19.18 86 24.65 6 23.20 16
23 62 12 6 18 31 17 24
28 18 24 37 29 14 25 28
0.33 38 0.80 24 0.79 25 0.57 31

The answer for row one should be "1"; row 2 should be "1"; row 3 should be
"0"; etc. I don't know if I'm being clear in the description of my needs.
At the end of each row I would like a formula that finds the heading of
"Rank" and evaluates the cell reference for that heading to determine
whether
or not it is less than 20 (<20). If it is, then I would like to have it
included in the count, if not, then discard it.

Note: I was able to achieve this outcome when I used "Countifs" in Excel
2007, but now I only have access to Excel 2003 and the "Countifs" formula is
not compatible with this older version.

Thanks for your assistance.

--
RD


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 19
Default Need function that will work in Excel 2003 like "Countifs" in

RagDyeR,

It was very helpful! Your help is greatly appreciated!
--
RD


"RagDyeR" wrote:

The answers you gave for your examples are wrong!

If you're using 8 columns, your returns for the 4 rows should be:

2, 1, 2, 0

With data entered in A1 to H5, with headers in Row 1,
This formula entered in I2, and copied down, will give the returns I
mentioned:

=SUMPRODUCT((A$1:H$1="Rank")*(A2:H2<20))
--

HTH,

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



"RD" wrote in message
...
The following is an example of my database. I am looking for a formula that
will provide me a count of the number of times a "Rank" is less than 20
(<20) at the end of each row.

Stat Rank Stat Rank Stat Rank Stat Rank
20.82 48 19.18 86 24.65 6 23.20 16
23 62 12 6 18 31 17 24
28 18 24 37 29 14 25 28
0.33 38 0.80 24 0.79 25 0.57 31

The answer for row one should be "1"; row 2 should be "1"; row 3 should be
"0"; etc. I don't know if I'm being clear in the description of my needs.
At the end of each row I would like a formula that finds the heading of
"Rank" and evaluates the cell reference for that heading to determine
whether
or not it is less than 20 (<20). If it is, then I would like to have it
included in the count, if not, then discard it.

Note: I was able to achieve this outcome when I used "Countifs" in Excel
2007, but now I only have access to Excel 2003 and the "Countifs" formula is
not compatible with this older version.

Thanks for your assistance.

--
RD



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Need function that will work in Excel 2003 like "Countifs" in

You're welcome, and thanks for the feed-back.
--

Regards,

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

"RD" wrote in message
...
RagDyeR,

It was very helpful! Your help is greatly appreciated!
--
RD


"RagDyeR" wrote:

The answers you gave for your examples are wrong!

If you're using 8 columns, your returns for the 4 rows should be:

2, 1, 2, 0

With data entered in A1 to H5, with headers in Row 1,
This formula entered in I2, and copied down, will give the returns I
mentioned:

=SUMPRODUCT((A$1:H$1="Rank")*(A2:H2<20))
--

HTH,

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



"RD" wrote in message
...
The following is an example of my database. I am looking for a formula
that
will provide me a count of the number of times a "Rank" is less than 20
(<20) at the end of each row.

Stat Rank Stat Rank Stat Rank Stat Rank
20.82 48 19.18 86 24.65 6 23.20 16
23 62 12 6 18 31 17 24
28 18 24 37 29 14 25 28
0.33 38 0.80 24 0.79 25 0.57 31

The answer for row one should be "1"; row 2 should be "1"; row 3 should be
"0"; etc. I don't know if I'm being clear in the description of my needs.
At the end of each row I would like a formula that finds the heading of
"Rank" and evaluates the cell reference for that heading to determine
whether
or not it is less than 20 (<20). If it is, then I would like to have it
included in the count, if not, then discard it.

Note: I was able to achieve this outcome when I used "Countifs" in Excel
2007, but now I only have access to Excel 2003 and the "Countifs" formula
is
not compatible with this older version.

Thanks for your assistance.

--
RD





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem getting "detect and repair" to work in Excel 2003 ms Excel Discussion (Misc queries) 4 February 4th 10 03:39 AM
=COUNTIFS(M3:M17,"=MAN",P3:P17,"=2000")+(COUNTIFS(M3:M17,"=LHR",P Shahzad Excel Worksheet Functions 1 July 2nd 08 08:39 AM
2007 - Column unhide in Excel does not work i.e. "Ctrl+shift+)" MikeElstonJones Excel Discussion (Misc queries) 3 November 28th 07 01:42 PM
Excel 2007 "Find" option won't work. Nerdophile Excel Worksheet Functions 2 September 19th 07 06:36 PM
Excel 2003 to 2007 Analysis ToolPack Errors ="#N/A" Amit Excel Worksheet Functions 8 August 6th 07 04:06 PM


All times are GMT +1. The time now is 01:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"