I have a workbook with 10 sheets with columns of text and a summary sheet. I
want to count how many times A, B, C, D appear on each of the sheets for each
number.
I've tried
=SUMPRODUCT(COUNTIF(INDIRECT("''Armitage:Viayra&'! B6"),A))
=SUMPRODUCT(COUNT(IF(Armitage:Viayra!$B$6,"D"),"A" ))
The first returns a #REF error and the second returns 0

Maybe you can be more informative, If you would have only one sheet what
would you do?
Do you only want to count in cell B6 in all sheets how many time A exists?
If so create a list of all 10 sheets (you cannot use first:last sheet), call
the list (insertnamedefine) something, let's say MySheets then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6"), "A"))

will count A in B6

If you want to count how many times A, B C and D occurs


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6"), {"A","B","C","D"}))



--
Regards,

Peo Sjoblom

(No private emails please)


"Gitel" wrote in message
...
I have a workbook with 10 sheets with columns of text and a summary sheet.
I
want to count how many times A, B, C, D appear on each of the sheets for
each
number.
I've tried
=SUMPRODUCT(COUNTIF(INDIRECT("''Armitage:Viayra&'! B6"),A))
=SUMPRODUCT(COUNT(IF(Armitage:Viayra!$B$6,"D"),"A" ))
The first returns a #REF error and the second returns 0

Peo,
The first scenario is the one I want. I actually have separate columns
for the "A", "B", "C", & "D", so once I get the formula correct I can just
copy it and change the criteria. There are 250 times I need to count the
instances. It's responses to questions. So I need to know for each question
how many times total the anwere was A, B, etc.
I tried your suggestion. Actually, I had tried it before I submitted my
question. The problem is, I get an error when I try to create the list. The
error says, A"formula in this worksheet contains one or more invalid
references." So it won't let me create the list.

"Peo Sjoblom" wrote:

Maybe you can be more informative, If you would have only one sheet what
would you do?
Do you only want to count in cell B6 in all sheets how many time A exists?
If so create a list of all 10 sheets (you cannot use first:last sheet), call
the list (insertnamedefine) something, let's say MySheets then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6"), "A"))

will count A in B6

If you want to count how many times A, B C and D occurs


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6"), {"A","B","C","D"}))



--
Regards,

Peo Sjoblom

(No private emails please)


"Gitel" wrote in message
...
I have a workbook with 10 sheets with columns of text and a summary sheet.
I
want to count how many times A, B, C, D appear on each of the sheets for
each
number.
I've tried
=SUMPRODUCT(COUNTIF(INDIRECT("''Armitage:Viayra&'! B6"),A))
=SUMPRODUCT(COUNT(IF(Armitage:Viayra!$B$6,"D"),"A" ))
The first returns a #REF error and the second returns 0

How do you create the list? One way, put ALL sheet names in question (if you
have 50 sheets you have to put all 50 sheet names in a range) preferably in
the summary sheet, assume you put them in H1:H50, select H1:H50 and type a
name in the name box (above column A header) and press enter, use my first
formula and replace MySheets with the name you gave the list, now if you
want to copy the formula across to check A in B6, D6 etc you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!"&CEL L("address",B6)),"A"))

if you want to count A in all sheets in the range B6 : IV6 you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6:IV 6"),"A"))




--
Regards,

Peo Sjoblom

(No private emails please)


"Gitel" wrote in message
...
Peo,
The first scenario is the one I want. I actually have separate columns
for the "A", "B", "C", & "D", so once I get the formula correct I can just
copy it and change the criteria. There are 250 times I need to count the
instances. It's responses to questions. So I need to know for each
question
how many times total the anwere was A, B, etc.
I tried your suggestion. Actually, I had tried it before I submitted my
question. The problem is, I get an error when I try to create the list.
The
error says, A"formula in this worksheet contains one or more invalid
references." So it won't let me create the list.

"Peo Sjoblom" wrote:

Maybe you can be more informative, If you would have only one sheet what
would you do?
Do you only want to count in cell B6 in all sheets how many time A
exists?
If so create a list of all 10 sheets (you cannot use first:last sheet),
call
the list (insertnamedefine) something, let's say MySheets then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6"), "A"))

will count A in B6

If you want to count how many times A, B C and D occurs


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6"), {"A","B","C","D"}))



--
Regards,

Peo Sjoblom

(No private emails please)


"Gitel" wrote in message
...
I have a workbook with 10 sheets with columns of text and a summary
sheet.
I
want to count how many times A, B, C, D appear on each of the sheets
for
each
number.
I've tried
=SUMPRODUCT(COUNTIF(INDIRECT("''Armitage:Viayra&'! B6"),A))
=SUMPRODUCT(COUNT(IF(Armitage:Viayra!$B$6,"D"),"A" ))
The first returns a #REF error and the second returns 0

Thanks. That worked.


"Peo Sjoblom" wrote:

How do you create the list? One way, put ALL sheet names in question (if you
have 50 sheets you have to put all 50 sheet names in a range) preferably in
the summary sheet, assume you put them in H1:H50, select H1:H50 and type a
name in the name box (above column A header) and press enter, use my first
formula and replace MySheets with the name you gave the list, now if you
want to copy the formula across to check A in B6, D6 etc you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!"&CEL L("address",B6)),"A"))

if you want to count A in all sheets in the range B6 : IV6 you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6:IV 6"),"A"))




--
Regards,

Peo Sjoblom

(No private emails please)


"Gitel" wrote in message
...
Peo,
The first scenario is the one I want. I actually have separate columns
for the "A", "B", "C", & "D", so once I get the formula correct I can just
copy it and change the criteria. There are 250 times I need to count the
instances. It's responses to questions. So I need to know for each
question
how many times total the anwere was A, B, etc.
I tried your suggestion. Actually, I had tried it before I submitted my
question. The problem is, I get an error when I try to create the list.
The
error says, A"formula in this worksheet contains one or more invalid
references." So it won't let me create the list.

"Peo Sjoblom" wrote:

Maybe you can be more informative, If you would have only one sheet what
would you do?
Do you only want to count in cell B6 in all sheets how many time A
exists?
If so create a list of all 10 sheets (you cannot use first:last sheet),
call
the list (insertnamedefine) something, let's say MySheets then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6"), "A"))

will count A in B6

If you want to count how many times A, B C and D occurs


=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B6"), {"A","B","C","D"}))



--
Regards,

Peo Sjoblom

(No private emails please)


"Gitel" wrote in message
...
I have a workbook with 10 sheets with columns of text and a summary
sheet.
I
want to count how many times A, B, C, D appear on each of the sheets
for
each
number.
I've tried
=SUMPRODUCT(COUNTIF(INDIRECT("''Armitage:Viayra&'! B6"),A))
=SUMPRODUCT(COUNT(IF(Armitage:Viayra!$B$6,"D"),"A" ))
The first returns a #REF error and the second returns 0