Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Count rows where a specific value appears in any of 4 columns

This is an example of the spreadsheet I'm working with:

A B C D
1 1 1 1
0 0 0 1
0 0 0 0
0 0 0 10
0 0 0 10
0 0 0 0
4 4 1 1
0 0 0 0
0 0 1 1
0 0 0 0
1 1 0 0

What I would like to do is count the number of rows where the value 1 occurs
in the four columns, wherever that might be.

The answer for the data above, for example, would be 5 (five rows contain
the value 1 somewhere in the four columns).

There are 5,807 rows of data in the worksheet and I'm using Excel 2003.

Thank you.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 345
Default Count rows where a specific value appears in any of 4 columns

Lisa
Try:
=SUM(IF((A2:A5807=1)*(B2:B5807=1)*(C2:C5807=1),D2: D5807))
The formula must be entered as array formula - press CTRL+SHIFT+ENTER
Hope this helps

"LisaM" wrote:

This is an example of the spreadsheet I'm working with:

A B C D
1 1 1 1
0 0 0 1
0 0 0 0
0 0 0 10
0 0 0 10
0 0 0 0
4 4 1 1
0 0 0 0
0 0 1 1
0 0 0 0
1 1 0 0

What I would like to do is count the number of rows where the value 1 occurs
in the four columns, wherever that might be.

The answer for the data above, for example, would be 5 (five rows contain
the value 1 somewhere in the four columns).

There are 5,807 rows of data in the worksheet and I'm using Excel 2003.

Thank you.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Count rows where a specific value appears in any of 4 columns

Hi Ron@Buy

I tried the formula and got a value which I can't check to see if it's
correct (because of the sheer number of rows) but I tried it for eight rows
of data and the value returned for those eight rows was incorrect.

Could you run me through the logic behind the formula?

Thank you!


"Ron@Buy" wrote:

Lisa
Try:
=SUM(IF((A2:A5807=1)*(B2:B5807=1)*(C2:C5807=1),D2: D5807))
The formula must be entered as array formula - press CTRL+SHIFT+ENTER
Hope this helps

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Count rows where a specific value appears in any of 4 columns

Hi,

In column E, use the formula COUNTIF(A2:D2,1)0 and copy all the way down to
row 5807. Now in a spare cell, use the formula =COUNTIF(E2:E5807,TRUE)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"LisaM" wrote in message
...
This is an example of the spreadsheet I'm working with:

A B C D
1 1 1 1
0 0 0 1
0 0 0 0
0 0 0 10
0 0 0 10
0 0 0 0
4 4 1 1
0 0 0 0
0 0 1 1
0 0 0 0
1 1 0 0

What I would like to do is count the number of rows where the value 1
occurs
in the four columns, wherever that might be.

The answer for the data above, for example, would be 5 (five rows contain
the value 1 somewhere in the four columns).

There are 5,807 rows of data in the worksheet and I'm using Excel 2003.

Thank you.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Count rows where a specific value appears in any of 4 columns

Hi Ashish,

Thank you for your help. This does this job!

Lisa

"Ashish Mathur" wrote:

Hi,

In column E, use the formula COUNTIF(A2:D2,1)0 and copy all the way down to
row 5807. Now in a spare cell, use the formula =COUNTIF(E2:E5807,TRUE)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Count rows where a specific value appears in any of 4 columns

You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"LisaM" wrote in message
...
Hi Ashish,

Thank you for your help. This does this job!

Lisa

"Ashish Mathur" wrote:

Hi,

In column E, use the formula COUNTIF(A2:D2,1)0 and copy all the way down
to
row 5807. Now in a spare cell, use the formula =COUNTIF(E2:E5807,TRUE)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com


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
Count the number of times a specific date appears in a range of ce Louisa Excel Worksheet Functions 3 May 19th 09 05:26 PM
count how many rows a word appears in learningaccess Excel Worksheet Functions 1 February 12th 09 10:48 PM
Count the times a specific character appears in a cell [email protected] Excel Worksheet Functions 3 December 18th 08 12:36 PM
Count the number of times specific text appears in a column Nannie Excel Worksheet Functions 5 August 27th 08 07:09 PM
count the number of times a specific word appears in a column BAR Excel Worksheet Functions 1 June 27th 06 05:03 PM


All times are GMT +1. The time now is 03:21 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"