Excel Formula - Wildcards in an IF Array

  • Thread starter Thread starter DJR
  • Start date Start date
D

DJR

I've got a spread sheet which i want to use as an annual leave plan and
what i've got is a column for each week in the year, a column detailing
what shift they are on and a column detailing if an employee is Full
Time or Part Time, in the weekly columns if someone is off on a Monday
and Tuesday for example i enter "MOTU" under that week. All i'm
basically trying to do count the number of "MO" 's taken by Fulltime
night shift workers but i can't seem to be able to use wildcard in
conditional sum arrays which i need to count the MO's in the MOTU's, is
there another type of Formula i can use or is there a way around this
problem?

Any tips Gratefully Received
Many Thanks In advance.
DJR

P.S. I'm using Excel 2000
 
Thanks, that seems to work to count the MO's but what is the syntax if
only want it to count the MO's in column A if there is a "FT" in colum
B, Excel help is a useful as ever!
Ta
DJ
 
=SUMPRODUCT((ISNUMBER(FIND("MO",A1:A100)))*(ISNUMBER(FIND("FT",B1:B!00))))
Click to expand...
...

Picky: could be condensed to the array formula

=COUNT(FIND("MO",A1:A100)*FIND("FT",B1:B100))
 
Back
Top