problem with IF function

[marcus]

I am trying to count the number of times certain data
shows up in several columns of my worksheet. For example,
I have a spreadsheet with four columns of info. Col a
lists the old department (one of four possibilities). Col
b lists the new department (one of 7 possibilities). Col c
lists the type of employee (one of two possibilites). And
Col d lists the month of the change (either Sept, Oct,
Nov).

Here is my formula:

=IF
((A2:A266="CHC",B2:B266="",C2:C266="FACTORY",d2:d266="Sept"
),COUNTA(A2:A266),"Nothing to show")

Basically, I want to know how many times there are
instances where the old department = CHC, the new
department is "blank", the type of employee is Factory,
and the month is Sept.

This always brings back the false of "Nothing to show"

 

[immanuel]

Try the following array formula:

=SUM(IF((A2:A266="CHC")*(B2:B266="")*(C2:C266="FACTORY")*(D2266="Sept"),1,
0))

If D2266 contains dates, as opposed to month names, replace

(D2266="Sept")

above with:

(Month(D2266)=9)

which would make your complete formula:

=SUM(IF((A2:A266="CHC")*(B2:B266="")*(C2:C266="FACTORY")*(Month(D2266)=9),
1,0))

* Remember that you'll need to array-enter (Ctrl-Shift-Enter) this formula
to make it work.

/i.

 

[Anders S]

Marcus,

I think this will work:

=SUMPRODUCT(--(A2:A266="CHC"),--(B2:B266=""),--(C2:C266="FACTORY"),--(D2266="S
ept"))

HTH
Anders Silvén