Conditional IF statements and totals

[lindasf]

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.

lindas

Attachment filename: position control pc03-04_041204 - inv only-forum.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=52636

 

[Frank Kabel]

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

 

[lindasf]

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

Attachment filename: position control pc03-04_041204 - inv only-forum.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=52648

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(C2:C100=1426),--(D2100=""))

and
=SUMPRODUCT(--(D2100=1632))

 

[lindasf]

Thank you Frank.

Your SUMPRODUCT statements work, but I was hoping there would be a mor
automatic way to do this.

There are many job codes involved and I would have to manually creat
many formulas to catch them all.

On another note, I did say "test for space" but I guess what I reall
meant is "test for space OR blank (e.g. null)".

Many of the "blank" cells in Column D are not counted (but I want the
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 quit
a bit of work.

Is there a way to test for space OR blank?

Thx much.

lindas

 

[lindasf]

Frank,

Thanks again for all your help over the last few weeks. I thought I'
re-ask the question as to whether there is a more automatic way to d
the counts (please see the bottom of this post).

It would be great if I could do it with VBA or macros.

Last night I had to manually enter 60 formulas (2 each for 30 jo
codes). As I have more spreadsheets to do, automating this someho
would be really great.

Thx. again.

lindas

 

[Frank Kabel]

Hi
you may have a look at pivot tables for this. See
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm