Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3d countif
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3d countif
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3d countif
=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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3d countif
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 , 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3d countif
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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3d countif
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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3d countif
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&"'!"&B1 6),C16)) and it returned the correct value -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mrfish" wrote in message ... 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" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
3d countif
In article ,
Mrfish 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&"'!"&C ELL("address",D3)),"D") ) ....where A2:A10 contains the sheet names. Hope this helps! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif or Something Else? | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |