I use EXCEL 2000 and have to run calculations on spreadsheets given t
me by users. Sometimes the users put in blanks (using delete or clear
and sometimes they hit the space bar. When I use the SUMPRODUC
provided to me by Frank, only the spaces are counted. This means that
have to manually go in and enter spaces in those fields. Of course
can use filldown, but the cells are often not contiguous. Can anyon
please tell me how to get around this? Thx. much.
PLEASE SEE VERY BOTTOM OF THIS POST. THANKS.
lindasf
Posted by lindasf on 04-27-2004 07:26 PM:
Conditional IF statements and totals
Logically, I would like to do the following:
(Both D2 and C2 refer to job codes).
If D2 = space, add 1 to the counter for value in C2
If D2 not equal space, add 1 to the counter for value in D2.
The end result would be that I have different totals for the variou
job codes.
I have tried using SUMPRODUCT and IF statements, but without any luck.
Attached is the file in question.
Thank you.
lindasf
--------------------------------------------------------------------------------
Posted by Frank Kabel on 04-27-2004 07:42 PM:
Re: Conditional IF statements and totals
Hi
even after looking at your file still not clear what you're trying to
achieve. Please post some example rows in plain text (no attachment)
and describe your expected result based on this example data
--
Regards
Frank Kabel
Frankfurt, Germany
Logically, I would like to do the following:
(Both D2 and C2 refer to job codes).
If D2 = space, add 1 to the counter for value in C2
If D2 not equal space, add 1 to the counter for value in D2.
The end result would be that I have different totals for the various
job codes.
I have tried using SUMPRODUCT and IF statements, but without any luck.
Attached is the file in question.
Thank you.
lindasf
Attachment filename: position control pc03-04_041204 - inv
only-forum.xls Download attachment:
http://www.excelforum.com/attachment.php?postid=526366 ---
Message posted
--------------------------------------------------------------------------------
Posted by lindasf on 04-27-2004 08:19 PM:
Conditional IF statements and totals
I am trying to create a counter for each job code. Since there are 6
rows in the file, the total of all the job code counters should equa
68.
Each row in the file refers to an employee (I have deleted their name
for obvious reasons). If an employee has a value in both the C and
columns, it means he has been promoted and the value in the D colum
should be used (and added to the counter for that value) instead of th
value in the C column.
A B C D
Row 2 INV 14260001 1426
Row 3 INV 14260002 1426
Row 9 INV 14260008 1426 1632
Row 14 INV 16320000.5 1632
In row 2, since D2 is blank, use the job code in C2 and add 1 to th
counter for the 1426 job codes.
In row 3, since D2 is blank, use the job code in C2 and add 1 to th
counter for the 1426 job codes.
In row 9, since D2 is NOT blank, use the job code in D2 and add 1 t
the counter for the 1632 job codes.
In row 14, since D2 is blank, use the job code in C2 and add 1 to th
counter for the 1632 job codes.
Expected result (for this 4 row file) would be:
1426 = 2
1632 = 2
P.S. I hope the columns align (they seem to have a mind of their own
to illustrate what I was saying. If not please refer to the attache
EXCEL and you will see the rows in question. Thx.
--------------------------------------------------------------------------------
Posted by Frank Kabel on 04-27-2004 09:10 PM:
Re: Conditional IF statements and totals
Hi
try
=SUMPRODUCT(--(C2:C100=1426),--(D2
100=""))
and
=SUMPRODUCT(--(D2
100=1632))
--
Regards
Frank Kabel
Frankfurt, Germany
I am trying to create a counter for each job code. Since there ar 68
rows in the file, the total of all the job code counters shoul equal
68.
Each row in the file refers to an employee (I have deleted their names
for obvious reasons). If an employee has a value in both the C and D
columns, it means he has been promoted and the value in the D column
should be used (and added to the counter for that value) instead of
the value in the C column.
A B C D
Row 2 INV 14260001 1426
Row 3 INV 14260002 1426
Row 9 INV 14260008 1426 1632
Row 14 INV 16320000.5 1632
In row 2, since D2 is blank, use the job code in C2 and add 1 to the
counter for the 1426 job codes.
In row 3, since D2 is blank, use the job code in C2 and add 1 to the
counter for the 1426 job codes.
In row 9, since D2 is NOT blank, use the job code in D2 and add 1 to
the counter for the 1632 job codes.
In row 14, since D2 is blank, use the job code in C2 and add 1 to the
counter for the 1632 job codes.
Expected result (for this 4 row file) would be:
1426 = 2
1632 = 2
P.S. I hope the columns align (they seem to have a mind of their own)
to illustrate what I was saying. If not please refer to the attached
EXCEL and you will see the rows in question. Thx.
Attachment filename: position control pc03-04_041204 - inv
only-forum.xls Download attachment:
http://www.excelforum.com/attachment.php?postid=526482 ---
Message posted
--------------------------------------------------------------------------------
Posted by lindasf on 04-27-2004 09:50 PM:
conditional IF statements and totals
Thank you Frank.
Your SUMPRODUCT statements work, but I was hoping there would be a more
automatic way to do this.
There are many job codes involved and I would have to manually create
many formulas to catch them all.
On another note, I did say "test for space" but I guess what I really
meant is "test for space OR blank (e.g. null)".
Many of the "blank" cells in Column D are not counted (but I want them
to be counted) because they contain a null value. I could, of course,
go in and change all the null values to spaces, but this would be quite
a bit of work.
Is there a way to test for space OR blank?
Thx much.
lindasf
