Count If

K

kimare

Could someone please help me with the following formula?

I would like to count somthing if two criterias are filled

The formula finds the data from 52 different workbooks name
1,2,3,......52

the first IF: COUNT cells B3:B100 in each workbook, if they got th
same text as C2 (in the same workbook as formula)
the second IF: within the first datas COUNT the cells R3:R100 with th
text "j"

I've tried this formula, but getting an error message

=COUNT(IF('1:52'!'B3:B100'=C2;IF('1:52'!;'R3:R100'="j")))

I hope someone is able to help
 
D

Domenic

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:52"))&"'!B3:B100"),C2)*COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:52"))&"'!R3:R100"),"j"))

Hope this helps!
 
B

Bob Phillips

Domenic,

Sure you have got a formula spanning workbooks? Looks like worksheets to me.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Domenic

Bob said:
Domenic,

Sure you have got a formula spanning workbooks? Looks like worksheet
to me.
Click to expand...

Hi Bob,

Actually, in this case the OP means worksheets. :)

Cheers
 
B

Bob Phillips

Hi Domenic,

They are a bit different :)

In that case, should you be INDIRECTing cell C2 as well?

Bob
 
D

Domenic

Bob said:
Hi Domenic,

They are a bit different :)

In that case, should you be INDIRECTing cell C2 as well?
Click to expand...

Hmmm,........INDIRECT-ly speaking, :) ........good question.
Although, after re-reading the post, I doubt it. Maybe the OP can
clarify this point.
 
B

Bob Phillips

He's probably still reeling from the formula :)

Domenic said:
Hmmm,........INDIRECT-ly speaking, :) ........good question.
Although, after re-reading the post, I doubt it. Maybe the OP can
clarify this point.
Click to expand...
 
K

kimare

Domenic said:
Hmmm,........INDIRECT-ly speaking, :) ........good question. Although
after re-reading the post, I doubt it. Maybe the OP can clarify thi
point.
Click to expand...


After all I doubt I'm able to clarify anything at all,

I have one workbook, with one called summary and then 52 worksheet
namned 1 to 52. Alltogheter 53 worksheets in one workbook.

The error message "there are an error in the formula".

I could send the entire workbook to you, if you would like to have
look at it.

Ki
 
D

Domenic

Hi Kim,

I just noticed that in your initial post you have a semi-colon instea
of a comma in your formula. So you're problem using a differen
version of Excel. Therefore, try replacing the commas in the formul
with a semi-colon instead...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:52"))&"'!B3:B100");C2);COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:52"))&"'!R3:R100");"j"))

Having said that, on second look, I don't think this formula will d
what you want. If not, an alternative would be to do a count on eac
sheet and then on your summary sheet do something like...

=SUM('1:52'!A1)

Hope this helps!
 
Top