I would like to do a 3d countif across multiple sheets. I simply want to
count the cells containing 'D's. Something like =COUNTIF(Start:End!D43,"D")
would be ideal if it worked!

Unfortunately I see now that countif() doesn't work with a 3d reference. I
see some other answers here have used a =sumproduct(n(... function to get
round this, but didn't understand it.

Can anyone here help?

I believe I found the thread you were looking for. It wasn't just
sumproduct(n( they used, it was sumproduct(n(threed(

Threed is part of an addon that turns a 3D reference into a 2D one, but in
the same addon there is countif.3D.

That addon is morefunc and can be downloaded (free) at:

http://xcell05.free.fr/morefunc/english/


"Mrfish" wrote:

> I would like to do a 3d countif across multiple sheets. I simply want to
> count the cells containing 'D's. Something like =COUNTIF(Start:End!D43,"D")
> would be ideal if it worked!
>
> Unfortunately I see now that countif() doesn't work with a 3d reference. I
> see some other answers here have used a =sumproduct(n(... function to get
> round this, but didn't understand it.
>
> Can anyone here help?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!D3"),"D"))
where A2:A4 holds the names of each sheet to be used -- extend if needed
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mrfish" <(E-Mail Removed)> wrote in message
news:03281BBF-E4FA-4F66-AC66-(E-Mail Removed)...
>I would like to do a 3d countif across multiple sheets. I simply want to
> count the cells containing 'D's. Something like
> =COUNTIF(Start:End!D43,"D")
> would be ideal if it worked!
>
> Unfortunately I see now that countif() doesn't work with a 3d reference. I
> see some other answers here have used a =sumproduct(n(... function to get
> round this, but didn't understand it.
>
> Can anyone here help?

Try...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A$10&"'!D43"),"D"))

....where A2:A10 contains the sheet names. Alternatively, if you
download and install the free add-in, Morefunc, you can use COUNTIF.3D.
The add-in can be download at...

http://xcell05.free.fr/morefunc/english/

Hope this helps!


In article <03281BBF-E4FA-4F66-AC66-(E-Mail Removed)>,
Mrfish <(E-Mail Removed)> wrote:

> I would like to do a 3d countif across multiple sheets. I simply want to
> count the cells containing 'D's. Something like =COUNTIF(Start:End!D43,"D")
> would be ideal if it worked!
>
> Unfortunately I see now that countif() doesn't work with a 3d reference. I
> see some other answers here have used a =sumproduct(n(... function to get
> round this, but didn't understand it.
>
> Can anyone here help?

Here is a UDF to do the same thing

Function tryme()
For Each wks In Worksheets
If wks.Range("D3") = "D" Then
mycount = mycount + 1
End If
Next
tryme = mycount
End Function

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mrfish" <(E-Mail Removed)> wrote in message
news:03281BBF-E4FA-4F66-AC66-(E-Mail Removed)...
>I would like to do a 3d countif across multiple sheets. I simply want to
> count the cells containing 'D's. Something like
> =COUNTIF(Start:End!D43,"D")
> would be ideal if it worked!
>
> Unfortunately I see now that countif() doesn't work with a 3d reference. I
> see some other answers here have used a =sumproduct(n(... function to get
> round this, but didn't understand it.
>
> Can anyone here help?

Genius. This is exactly what I wanted and I've made it work in one cell.

However I don't really understand how it works, can you explain?

Second small issue - I want to copy the formula out across rows and columns,
and currently the D3 part is fixed. Is there an easy change to unfix the
reference?

Many thanks!

"Bernard Liengme" wrote:

> =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!D3"),"D"))
> where A2:A4 holds the names of each sheet to be used -- extend if needed
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "Mrfish" <(E-Mail Removed)> wrote in message
> news:03281BBF-E4FA-4F66-AC66-(E-Mail Removed)...
> >I would like to do a 3d countif across multiple sheets. I simply want to
> > count the cells containing 'D's. Something like
> > =COUNTIF(Start:End!D43,"D")
> > would be ideal if it worked!
> >
> > Unfortunately I see now that countif() doesn't work with a 3d reference. I
> > see some other answers here have used a =sumproduct(n(... function to get
> > round this, but didn't understand it.
> >
> > Can anyone here help?
>
>
>

If you have XL 2003, use View/Toolbars to show the Formula Auditing toolbar,
select the cell with the SUMPRODCUT, use the Evaluate Function item (last
icon) on the Formula Auditing toolbar, This should help you see how the
formula evolves

I typed a D into cell C16 and modified my formula to read
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!D3"),C16))
and it returned the correct value

This change lets me copy the formula to other cells
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A$4&"'!D3"),C18))

I typed a D3 into cell B16 and modified my formula to read
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A$4&"'!"&B16),C16))
and it returned the correct value
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mrfish" <(E-Mail Removed)> wrote in message
news:AADC6D25-D86F-4F29-B991-(E-Mail Removed)...
> Genius. This is exactly what I wanted and I've made it work in one cell.
>
> However I don't really understand how it works, can you explain?
>
> Second small issue - I want to copy the formula out across rows and
> columns,
> and currently the D3 part is fixed. Is there an easy change to unfix the
> reference?
>
> Many thanks!
>
> "Bernard Liengme" wrote:
>
>> =SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!D3"),"D"))
>> where A2:A4 holds the names of each sheet to be used -- extend if needed
>> best wishes
>> --
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>> "Mrfish" <(E-Mail Removed)> wrote in message
>> news:03281BBF-E4FA-4F66-AC66-(E-Mail Removed)...
>> >I would like to do a 3d countif across multiple sheets. I simply want to
>> > count the cells containing 'D's. Something like
>> > =COUNTIF(Start:End!D43,"D")
>> > would be ideal if it worked!
>> >
>> > Unfortunately I see now that countif() doesn't work with a 3d
>> > reference. I
>> > see some other answers here have used a =sumproduct(n(... function to
>> > get
>> > round this, but didn't understand it.
>> >
>> > Can anyone here help?
>>
>>
>>

In article <AADC6D25-D86F-4F29-B991-(E-Mail Removed)>,
Mrfish <(E-Mail Removed)> wrote:

> Second small issue - I want to copy the formula out across rows and columns,
> and currently the D3 part is fixed. Is there an easy change to unfix the
> reference?

Try...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A$10&"'!"&CELL("address",D3)),"D")
)

....where A2:A10 contains the sheet names.

Hope this helps!

[emilycora]

Thank you so much, I love you guys!!!! I have a HUGE spreadsheet using morefunc's countif.3d spreadsheet, and I upgraded to Excel 2007 and it stopped working. I have been trying to figure out how to fix it for months!!!