problem with IF function

  • Thread starter Thread starter marcus
  • Start date Start date
M

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"
 
Try the following array formula:

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

If D2:D266 contains dates, as opposed to month names, replace

(D2:D266="Sept")

above with:

(Month(D2:D266)=9)

which would make your complete formula:

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

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

/i.
 
Marcus,

I think this will work:

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

HTH
Anders Silvén
 
Back
Top