Countif from multiple pages

[Clyde Lomax]

Good evening,

I need help again. I have 31 sheets labeled 1st, 2nd ... 31st.

Data on column "e2:e15" on each sheet that I want to countif to a summary
page that has all of the possiblities of "e2:e15".

I have the summary page laid out with the tab names from b1 through af1.
and a2:a25 with the possible data from sheets 1st - 31st.


I have tried =COUNTIF(INDIRECT(B$1&"!$e$2:$e$15"),$A2) it only works on
the column for sheet "2nd".

I have attempted to work with a "Sumproduct" formula that I copied from
another site that Mr. Grove was using to assist someone. I can't get the
below to work as well. I made a "wag" at the {"e2:e15"}),"a2")) and that
got me no where.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"1st","2nd","3rd","4th",
"5th","6th","7th","8th","9th","10th","11th","12th","13th","14th","15th",
"16th","17th","18th","19th,"20th","21th","22nd","23rd","24th","25th",
"26th","27th","28th","29th","30 th",31st}&"'!"&{"e2:e15"}),"a2"))

I would much prefer to use a countif if at all possible.

Thanks in advance.

Lomax

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(COUNTIF(INDIRECT("'" & B$1:$AF$1 & "'!$e$2:$e$15"),$A2)

 

[Clyde Lomax]

Frank,

Thanks but I am getting a #REF!.

lomax

Hi
try
=SUMPRODUCT(COUNTIF(INDIRECT("'" & B$1:$AF$1 & "'!$e$2:$e$15"),$A2)

 

[hgrove]

Clyde Lomax wrote...

Thanks but I am getting a #REF!.

...

"'!$e$2:$e$15"),$A2)

Frank's formula is missing a right parenthesis at the end, but onc
it's added his formula should work. Are *you* sure there are no stra
space characters in your worksheet names? The text values in B1:AF
must match the worksheet names *exactly* for this to work, and stra
spaces are the most likely cause of errors

 

[Clyde Lomax]

Don't understand it but I re-typed the tab name 1st, 2nd, etc and it works.

Oh well.

Thanks again and it was my oops.

Lomax