Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using countif
I am trying to select the same cell (B7) in several excel sheets that are
located in the same workbook, this cell may or may not contain an X, those that do I want the total amount in a separate sheet. Steps used a =countif( select the fisrt sheet, hold the shift key and selceted the last sheet, select the cell and then complete the formla, hit enter. The formula is: =countif('Survey 1:Survey 13'!B7,"X") the result result is #Value! The value should have been 12, what am I doing wrong? TLazio |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using countif
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Survey 1","Survey 2","Survey 3","Survey
4","Survey 5"}&"'!B7"),"X")) or something similar extended for more sheets?? -- Gary's Student "Tlazio" wrote: I am trying to select the same cell (B7) in several excel sheets that are located in the same workbook, this cell may or may not contain an X, those that do I want the total amount in a separate sheet. Steps used a =countif( select the fisrt sheet, hold the shift key and selceted the last sheet, select the cell and then complete the formla, hit enter. The formula is: =countif('Survey 1:Survey 13'!B7,"X") the result result is #Value! The value should have been 12, what am I doing wrong? TLazio |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using countif
From the help file:
You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA. But you may be able to do it w/ an array formula. This seems to work okay on my machine: =SUM(COUNTIF(INDIRECT("'Survey "&Row(Indirect("1:13"))&"'!B7"),"X")) entered with Control+Shift+Enter. Another possible way - if the cell will either be "X" or blank, perhaps you could use a 1 or 0, then use the SUM function in a 3D reference. =SUM('Survey 1:Survey 13'!B7)) If you want an "X" to be displayed when the cell value is a 1 and blank for a 0, you could try a custom format of [=1]"X";; and I might use Data Validation to restrict the input to values between 0 and 1 (if the cell is actually an input cell and not the result of a formula). "Tlazio" wrote: I am trying to select the same cell (B7) in several excel sheets that are located in the same workbook, this cell may or may not contain an X, those that do I want the total amount in a separate sheet. Steps used a =countif( select the fisrt sheet, hold the shift key and selceted the last sheet, select the cell and then complete the formla, hit enter. The formula is: =countif('Survey 1:Survey 13'!B7,"X") the result result is #Value! The value should have been 12, what am I doing wrong? TLazio |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using countif
Try this:
=SUMPRODUCT(COUNTIF(INDIRECT("'Survey "&ROW(INDIRECT("1:13"))&"'!B7"),"x")) Biff "Tlazio" wrote in message ... I am trying to select the same cell (B7) in several excel sheets that are located in the same workbook, this cell may or may not contain an X, those that do I want the total amount in a separate sheet. Steps used a =countif( select the fisrt sheet, hold the shift key and selceted the last sheet, select the cell and then complete the formla, hit enter. The formula is: =countif('Survey 1:Survey 13'!B7,"X") the result result is #Value! The value should have been 12, what am I doing wrong? TLazio |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using countif
Not sure if my post made it - so I'll try one more time (amended slightly to
use Sumproduct instead of Sum in an array formula after seeing Gary's Student formula): From the help file: You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA. This seems to work okay on my machine: =SUMPRODUCT(COUNTIF(INDIRECT("'Survey "&Row(Indirect("1:13"))&"'!B7"),"X")) Another possible way - if the cell will either be "X" or blank, perhaps you could use a 1 or 0, then use the SUM function in a 3D reference. =SUM('Survey 1:Survey 13'!B7)) If you want an "X" to be displayed when the cell value is a 1 and blank for a 0, you could try a custom format of [=1]"X";; and I might use Data Validation to restrict the input to values between 0 and 1 (if the cell is actually an input cell and not the result of a formula). "Tlazio" wrote: I am trying to select the same cell (B7) in several excel sheets that are located in the same workbook, this cell may or may not contain an X, those that do I want the total amount in a separate sheet. Steps used a =countif( select the fisrt sheet, hold the shift key and selceted the last sheet, select the cell and then complete the formla, hit enter. The formula is: =countif('Survey 1:Survey 13'!B7,"X") the result result is #Value! The value should have been 12, what am I doing wrong? TLazio |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using countif
Thanks, I was able to get the results I was looking for.
TLazio "Gary''s Student" wrote: =SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Survey 1","Survey 2","Survey 3","Survey 4","Survey 5"}&"'!B7"),"X")) or something similar extended for more sheets?? -- Gary's Student "Tlazio" wrote: I am trying to select the same cell (B7) in several excel sheets that are located in the same workbook, this cell may or may not contain an X, those that do I want the total amount in a separate sheet. Steps used a =countif( select the fisrt sheet, hold the shift key and selceted the last sheet, select the cell and then complete the formla, hit enter. The formula is: =countif('Survey 1:Survey 13'!B7,"X") the result result is #Value! The value should have been 12, what am I doing wrong? TLazio |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using countif
This worked for me as well.
Thanks! TLazio "JMB" wrote: Not sure if my post made it - so I'll try one more time (amended slightly to use Sumproduct instead of Sum in an array formula after seeing Gary's Student formula): From the help file: You can use 3-D references to refer to cells on other sheets, to define names, and to create formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA. This seems to work okay on my machine: =SUMPRODUCT(COUNTIF(INDIRECT("'Survey "&Row(Indirect("1:13"))&"'!B7"),"X")) Another possible way - if the cell will either be "X" or blank, perhaps you could use a 1 or 0, then use the SUM function in a 3D reference. =SUM('Survey 1:Survey 13'!B7)) If you want an "X" to be displayed when the cell value is a 1 and blank for a 0, you could try a custom format of [=1]"X";; and I might use Data Validation to restrict the input to values between 0 and 1 (if the cell is actually an input cell and not the result of a formula). "Tlazio" wrote: I am trying to select the same cell (B7) in several excel sheets that are located in the same workbook, this cell may or may not contain an X, those that do I want the total amount in a separate sheet. Steps used a =countif( select the fisrt sheet, hold the shift key and selceted the last sheet, select the cell and then complete the formla, hit enter. The formula is: =countif('Survey 1:Survey 13'!B7,"X") the result result is #Value! The value should have been 12, what am I doing wrong? TLazio |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct vs. countif | Excel Discussion (Misc queries) | |||
Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions |