Counting text across multiple sheets with a specific criterion

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gitel - 12 Nov 2005 17:21 GMT

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

Reply to this Message

Peo Sjoblom - 12 Nov 2005 18:26 GMT

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 (insert>name>define) 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"}))

Signature

Regards,

Peo Sjoblom

(No private emails please)

>I have a workbook with 10 sheets with columns of text and a summary sheet.
>I
[quoted text clipped - 5 lines]
> =SUMPRODUCT(COUNT(IF(Armitage:Viayra!$B$6,"D"),"A"))
> The first returns a #REF error and the second returns 0

Reply to this Message

Gitel - 12 Nov 2005 22:31 GMT

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.

> Maybe you can be more informative, If you would have only one sheet what
> would you do?
[quoted text clipped - 19 lines]
> > =SUMPRODUCT(COUNT(IF(Armitage:Viayra!$B$6,"D"),"A"))
> > The first returns a #REF error and the second returns 0

Reply to this Message

Peo Sjoblom - 13 Nov 2005 07:17 GMT

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&"'!"&CELL("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:IV6"),"A"))

Signature

Regards,

Peo Sjoblom

(No private emails please)

> Peo,
> The first scenario is the one I want. I actually have separate columns
[quoted text clipped - 36 lines]
>> > =SUMPRODUCT(COUNT(IF(Armitage:Viayra!$B$6,"D"),"A"))
>> > The first returns a #REF error and the second returns 0

Reply to this Message

Gitel - 13 Nov 2005 14:19 GMT

Thanks. That worked.

> 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
[quoted text clipped - 49 lines]
> >> > =SUMPRODUCT(COUNT(IF(Armitage:Viayra!$B$6,"D"),"A"))
> >> > The first returns a #REF error and the second returns 0

Reply to this Message