calculations

[Guest]

I have a file like

Dept PayType Amount
2312 OT 500
reg 1000
vac 10
ss 5
2566 ot 5522
reg 42
vac 565 etc
2556 reg 10
I would like to automate the process of cumulating the
amounts. For eg: I need to sum "reg" and "ot" for "2312"
and "2556" only. I think the logic should go something
like this - look in paytype and find "reg" or "ot", if
found, move left and if dept is "blank", go up until you
get a value. Now, if that is "2312" or "2556",
sum"amount".

I am lost coding this. please help
Thanks
..

 

[Frank Kabel]

Hi
could you fill column A in all rows with the dep. ID this way you could
use SUMPRODUCT for this. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Guest]

I want to avoid manually filling in the rows. I would
like to do either of 2 things.

1. Automate the process of filling in the Dept if the
field below is empty or
2. For a particular amount, go left, get Paytype and then
left to get dept, if dept field is empty, go up until you
come accross a value.

Thanks

 

[Domenic]

If you follow Frank's suggestion and fill Column A with department ID's,
you can use the following formula to sum 'ot' and 'reg' amounts for
departments 2312 and 2556...

=SUMPRODUCT((A2:A9={2312,2556})*ISNUMBER(MATCH(B2:B9,{"ot","reg"},0))*C2:
C9)

OR

=SUMPRODUCT((A2:A9={2312,2556})*ISNUMBER(MATCH(B2:B9,E2:E3,0))*C2:C9)

....where E2 contains the first PayType of interest, and E3 the second.

Hope this helps!

 

[Frank Kabel]

Hi
for automating this process try:
- select column A
- hit F5 and click 'Special'
- choose 'Empty cells'
- enter the equation sign '=' and hit the upper arrow key
- now commit the entry with CTRL+ENTER