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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com