Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count the number of times a specific date appears in a range of ce | Excel Worksheet Functions | |||
count how many rows a word appears in | Excel Worksheet Functions | |||
Count the times a specific character appears in a cell | Excel Worksheet Functions | |||
Count the number of times specific text appears in a column | Excel Worksheet Functions | |||
count the number of times a specific word appears in a column | Excel Worksheet Functions |