Countif formula

[Zak]

I want to use the countif function (or another that i may not be aware of) to
count a certain number of things.. but the problem is i have many things i
want it to count and the excel wizard does not let me add another argument,
it appears the countif formula is just range and criteria with no additional
extras. If this is the case that an additional argument cannot be added is
there another way to do the below?

(without explaining what the below means) here it is what i am wanting some
excel function (be it a macro or formula) to do:

A large spreadsheet containing masses of data is used to analyse certain
numbers for things. The spreadsheet is divided out by line of service for
company and how many people work in each line of service. Each line of
service is acronymed:

GTS - INDIA, GTS - NEN, GTS - NEC, GTS - NES, GTS etc and this is not the
end. it continues with a few more GTS.. and then also includes many other
acronymed line of services. i want to use an excel function that will count
the number of people in each line of service.

Heres the problem: because the acronymed line of service name is not just
GTS (bar one) but contains an additional few letters (as above) depending on
region i cant seem to use the countif funtion to count everything above. the
countif formula seems to just say "what is the criteria" so i put in GTS but
this will obviously bring back everything just containing GTS and not GTS
INDIA, GTS NEN etc. how do i do the formula/macro so that it can count
everything i.e i should be able to say 'if includes' the letters GTS (so
should bring back everything) as opposed to '=GTS' which only brings back GTS.

Also is there a way to specify a date range within the formula? for example,
i just want to know the number of people in all the different GTS fields in
jan, feb etc.

sorry about the long message. any help would be much appreciated.

 

[Pete_UK]

You can use wildcard characters in COUNTIF, so if you want to count
all employees where the line of service begins with GTS, you can use
"GTS*" as the criteria.

You can only use COUNTIF where you have one condition - for multiple
conditions (like including a date range), you will have to use
SUMPRODUCT or SUM( IF( used as an array formula.

Hope this helps.

Pete

 

[Zak]

I tried the countif with the * and it worked a treat, thanks for that. but
how am i supposed to specify the date range witin the same formula? i know
you said to use sum product or sumif but i have to use countif to count the
GTS etc so have to use the date range within the same formula too. i want it
to say if date is between 1-1-08 & 31-01-08 then return this... But then this
means the formula would become outdated each month and it would have to be
manually updated, is there not a way to automate this?

Also, in the same sheet i am trying to record a macro so that is automates
some manual tasks.. how can i tell the macro to recognise a cell range so
that it recognises what to do regardless of whether the sheet has extra cells
in it the next time. for example, if i copy the cell range j1:j32 because of
what it contains what if next time it is a few extra cells or a few less. how
would excel recognise what to do?

thanks a lot.

 

[Pete_UK]

To re-iterate - if you have a single condition to count, then use
COUNTIF, but if you have 2 or more conditions you have to use
something else. The "something else" will still give you a count of
the items, like so:

=SUMPRODUCT((condition_1)*(condition_2)*(condition_3))

even though it starts with the word "SUM". This will give you a count
where 3 conditions apply. You haven't given any details of the columns
you use, so assuming your dates are in column D, occupying D250,
then one of your conditions might be:

(D250>=DATE(2008,1,1))

or if you put the start date in cell M2, for example, and the end date
of your range in N2, then you would have something like:

=SUMPRODUCT((D250>=M2)*(D250<=N2)*(ISNUMBER(SEARCH("GTS",B2:B50))))

This assumes that your line of service is in column B, and that you
have 50 rows of data. You can adjust this if you have more, but it
doesn't matter if you have less as the extra rows covered by the
formula will not count anything.

Hope this helps.

Pete

 

[Zak]

I tried the formula but it still doesnt work.

here it is:

=SUMPRODUCT('Resource Details'!G2:G1785=GTS,'Resource
Details'!W2:W1785>=DATE(1,10,2007),'Resource
Details'!W2:W1785>=DATE(31,10,2007))

i need to specify a date range.. i.e. if it is between the month of october.
what am i doing wrong? does the gts bit need to be in brackets followed by an
*?

thanks so much.

 

[Zak]

Dont worry about it mate, i finally got it to work!

thanks for all your help.

 

[David Biddulph]

You may wish to go back into Excel help to remind yourself of the syntax of
the DATE function, Zak?

And also look at your GTS condition. You may want
=SUMPRODUCT(LEFT('Resource Details'!G2:G1785,3)="GTS"...

And then you need to look at your product. Pete's suggestion was to use a
multiply between the conditions. If you separate the terms by a comma,
you'll need the double unary minus to coerce the boolean TRUE/FALSE to a
number 1/0 to multiply.

 

[Pete_UK]

Glad to hear it, Zak.

Pete

Dont worry about it mate, i finally got it to work!

thanks for all your help.






- Show quoted text -