i want to create attendance list for different employees in my company and give them ticks to monitor their presence or absence and be able to count at the end of the month in excel
> On Monday, March 02, 2009 12:59 AM Officeuse wrote:
> I'm trying to count specific cells in my column of text. The CountA would
> work except there are some cells that I need excluded. These cells all begin
> with the word "assist" so I tried the following function which isn't given my
> the expected results.
>
> =COUNTA(F7:F36)-COUNTIF(F7:F36,"assist*")
>
> taking the small example below, I need it to calculate the answer 3 but it's
> calculating 4 (4 is answer of just the COUNTA portion so I don't think it's
> doing anything with my subtraction and COUNTIF portion)
>
> MA-90
> blank
> MB-90
> MA-90
> blank
> assist MB-90
>
> This spreadsheet is a grid of sorts so I need the cells with assist to stay
> in this column; otherwise, I would separate the data and count again but that
> won't work in this case.
>
> Hope this all make sense. Thanks for any assistance you can provide.
> Marcia
>> On Monday, March 02, 2009 1:39 AM T. Valko wrote:
>> There's nothing wrong with your formula so if the result is incorrect you
>> may have unseen characters in those cells you think are blank. Are there
>> formulas in these cells that might return a formula blank ("") ? COUNTA will
>> count formula blanks.
>>
>> Try one of these and see what happens:
>>
>> =COUNTIF(F7:F36,"> ")-COUNTIF(F7:F36,"assist*")
>>
>> =SUMPRODUCT(--(LEN(TRIM(F7:F36))>0))-COUNTIF(F7:F36,"assist*")
>>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Office user" <(E-Mail Removed)> wrote in message
>> news:6D9829A2-9593-42C7-8805-(E-Mail Removed)...
>>> On Monday, March 02, 2009 2:55 AM Officeuse wrote:
>>> Thank you for your help. I tried your other 2 formulas and am still getting
>>> my unexpected results. There are no formulas at all in my blank cells but I
>>> went through each cell again anyway and cleared everything (Edit menu). I'm
>>> guessing at this point it must have to do with my data but I'm not sure where.
>>>
>>> Thanks for the help,
>>> Marcia
>>>
>>>
>>> "T. Valko" wrote:
>>>> On Monday, March 02, 2009 3:01 AM Officeuse wrote:
>>>> Viola! I found it. I went through each cell again and discovered one of
>>>> them had a space before my word "assist" so it threw off the COUNTIF. Thanks
>>>> again for your help.
>>>> Marcia
>>>>
>>>>
>>>> "T. Valko" wrote:
>>>>> On Monday, March 02, 2009 3:52 AM shanedevenshir wrote:
>>>>> Hi,
>>>>>
>>>>> In 2007 you could use
>>>>>
>>>>> =COUNTIFS(F7:F36,"<>assist*",F7:F36,"<>")
>>>>>
>>>>> In 2003 you can use
>>>>>
>>>>> =SUMPRODUCT(--(F7:F36<>""))-COUNTIF(F7:F36,"assist*")
>>>>>
>>>>> --
>>>>> If this helps, please click the Yes button.
>>>>>
>>>>> Cheers,
>>>>> Shane Devenshire
>>>>>
>>>>>
>>>>> "Office user" wrote:
>>>>>> On Monday, March 02, 2009 12:03 PM T. Valko wrote:
>>>>>> Good deal. Thanks for the feedback!
>>>>>>
>>>>>> --
>>>>>> Biff
>>>>>> Microsoft Excel MVP
>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>> ObjectDumper LINQ To Export Collection Via .NET Reflection
>>>>>> http://www.eggheadcafe.com/tutorials...eflection.aspx
Similar Threads
