Count If

[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

 

[Domenic]

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

Hope this helps!

 

[Domenic]

Still I get an error message........

The formula works fine for me. What type of error are you getting

 

[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)

 

[Domenic]

Domenic,

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

Hi Bob,

Actually, in this case the OP means worksheets.

Cheers

 

[Bob Phillips]

Hi Domenic,

They are a bit different

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

Bob

 

[Domenic]

Hi Domenic,

They are a bit different

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

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

 

[Bob Phillips]

He's probably still reeling from the formula

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

 

[kimare]

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

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

 

[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!