Dcount

[airplanepilot30]

Hey,

I am trying to get Excel to analyize 12 sheets to see how many times certain criteria come up. I can't seem to get the format right. Does anyone know what formula may work better or how to properly syntax this one to make the function work?

Thanks.

[protonLeah]

Is this a serious request for assistance?

1. No description of the workbooks/worksheets
2. No sample data provided:
   "...how many times certain criteria (?) come up..."
   Criteria are the rules you apply to the data to make comparisons, counts,
   etc. so you must specify the criteria.
3. No sample code or formulas:
   • "...what formula may work better..." (than what?)
   • ...how to properly syntax this one to make the function work?...

[airplanepilot30]

Of course its serious.

Ok... I have 12 worksheets in an excel thingy. In a colummn I have numbers that identify flights. I want excel to tell me how many times I have done the flight. Here is an example:

March

Day Flight Captain Purser Route Flight Time Block Time Journey RMK
1 150 Ace McCool Crabby McSlut Chicago/Kuugaruk 4.2 4.1 123456 Night

I want to know how many times I have done flight 150 or flown with Ace McCool.

Hope that makes it a little less ambiguous.

[airplanepilot30]

Can someone just tell me if this isn't possible?

Thanks

[Paul]

Hi pilot,

It's possible, but there isn't a shortcut to doing it outside of writing a macro. Using just worksheet functions it would be something like:

Please Login or Register  to view this content.

The same would apply for Ace McCool, but put it in quotes, =COUNTIF(Sheet1!C:C,"Ace McCool").

Instead of typing the 150 and "Ace McCool" into the formula, you could put cell references and then just put the values you're looking for into those cells. For example, if you put 150 into D18:
=COUNTIF(Sheet1!B:B,D18)+COUNTIF... etc.

[starguy]

Can someone just tell me if this isn't possible?

Thanks

Another way if you want to avoid typing long formula.
Find a blank cell in each sheet to be used for calculating the cells which contain 150 or other crieteria. The cell should be the same in all sheets e-g B101 in each sheet etc.
Click on the first sheet tab and select cell B100 then hold down Shift key and click on the last sheet tab (it will select all the sheet tabs). Now in formula bar type following formula.

=COUNTIF(B1:B100,150) and press Enter (this formula will be entered in all sheets in cell B100 as all the sheets were selected).

Then use SUM function to sum all the counts in cell B100 of all sheets.

=SUM(Sheet1:Sheet12!B100)

Hope this will help you.

[airplanepilot30]

Thanks very much for the help.

[airplanepilot30]

Ok, this is working! Now is there a way to make Excel slightly less picky. I have some days where I do a pairing like Flight# 120 and 150 which is entered as 120/150. Will excel look for just a partial match? like the 120 side?

Thanks.

[vane0326]

If I understand you correctly you want to countif across multiple worksheets by using a wild card right? try...

List your worksheet names in cells A2:A10


Input formula in cell B2

=COUNT(N(COUNTIF(INDIRECT("'"&A2:A10&"'!B1:B100"),"*"&150&"*")))


Note: The formula is an array need to hold down

Ctrl,Shift,Enter

Look at the link below.

http://office.microsoft.com/en-us/ex...000201033.aspx


Hope it helps!

[airplanepilot30]

Thanks. So is the '&' a wildcard?

[vane0326]

Thanks. So is the '&' a wildcard?

No this is:

*

[airplanepilot30]

So I could search for '*/120'? and that would give anything /120?

[vane0326]

So I could search for '*/120'? and that would give anything /120?

No like this.

Anything Right of the text

"*"&/120


Left of the text

/120&"*"

Middle of the text

"*"&/120&"*"

[airplanepilot30]

Perfect. Thanks. I am new to this forum but I do like playing with excel and am finding this a great resource.