Is DCOUNT the right formula to use??

[hollyequine]

Hello fellow Excelions:

Here is my issue, I have a name range called all_colorsheets. It looks like the below.

<TABLE style="WIDTH: 361pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=482 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>A</TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64>B</TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64>C</TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64>D</TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 59pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=79>E</TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 51pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=68>F</TD><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 59pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=79>G</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Name</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Carpet1</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Carpet2</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Wood</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Total</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Sub Carpet</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Sub Wood</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>John</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="500">$500 </TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="1000">$1,000 </TD><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num="1500"> $ 1,500 </TD><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num="500"> $ 500 </TD><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:num="1000"> $ 1,000 </TD></TR></TBODY></TABLE>
Note: there is a formula in my cells F and G for the subtotal of each material category. I am running IF(SUM(B1,C1)=0,"",SUM(B1,C1))

Let's just say this formula is in cell H3 if the above table was real. Therefore, I will have some cells that show a value (the sum of B & C) and other cells will be blank based on my IF formula.

Now, in a separate sheet I previously had a DCOUNT that grabbed the number of times columns B, C or D had a $ value present. Now I have shifted over my fields to pull the counts from my subtotal columns, F and G in this example. However, it isn't working - my guess is because those cells have formulas in them.

Is DCOUNT the best formula to use? I need to be able to count the number of times $ values pop up based on my IF function.

Help please & thank you! I have read through the other threads and can't seem to find a similar issue, I apologize if I missed this being answered already.

 

[baitmaster]

looks like its counting because you are returning the null value ""

try =countif([cells],">0"), something like that

 

[hollyequine]

thanks baitmaster, but I tried this option and could not get it to function correctly.

Because I have my name range located on another worksheet, my DCOUNT formula is a bit more complex, it is actually:

=DCOUNT(all_colorsheets,102,$A$123:$D$124)-DCOUNT(all_colorsheets,102,$A$120:$D$121)

I am asking it count the number of $ value hits that meet a criteria that I have set forth as being between A123 and D124 and subtracting any criteria that meet A120 to D121. It worked swimmingly when I only had either a $ value or a BLANK cell....now I am asking it to decipher between a cell with a $ value and a cell with just the IF formula listed.

Does this help clarify?

 

[hollyequine]

Post deleted, sorry I figured out this small portion of my issue....

 

[baitmaster]

Sorry, I'm still not entirely sure of your requirement, but I suspect that perhaps array formulae might help. Try the following:
http://www.cpearson.com/excel/ArrayFormulas.aspx

These can be slow if you have a lot of them, but they're generally a bit more flexible imo, than the D functions

They're good for summing / averaging, counting etc when certain criteria are met

 

[hollyequine]

Thank you!! I will try that - I really appreciate your help.