I have 4 drivers each with a user ID unique to them (ex: AA12345) each
of these drivers does a work function of inventoring items which shows
as either "INVEN01" or "INVEN02" on my report. "J" column for ID and
"M" column for function code, then in column "S" is the time spent on
that function. I would like to create a formula to Sum the time for
each driver on function INVEN01 and INVEN02. I tired coming up with
something for just for INVEN01 first - figuring I could do the same for
INVEN02 and add them together. I got this:

SUM(IF((YMS_TEST.xls!$J$2:$J$8000=AA12345)+(YMS_TE ST.xls!$M$2:$M$8000="INVEN01"),YMS_TEST.xls!$S$2:$ S$8000,0))

YMS_Test.XLS is the file my report comes out in. B4 refers to the
drivers ID. I thought this would reference the file and only sum the
time for rows were the ID and function were AA12345+INVEN01, but
instead I got rows added were the ID matched plus rows were the
function matched. Is there a way to make the system check both?

Thanks!


--
mgarcia
------------------------------------------------------------------------
mgarcia's Profile: http://www.excelforum.com/member.php...o&userid=30584
View this thread: http://www.excelforum.com/showthread...hreadid=509188

I haven't tested your formula, but from reading your description, try
changing the + to an *. Basically, you use + when you want an "or" and *
when you want "and" and from your description, it seems you want "and". So
your new formula would be:

SUM(IF((YMS_TEST.xls!$J$2:$J$8000=AA12345)*(YMS_TE ST.xls!$M$2:$M$8000="INVEN01"),YMS_TEST.xls!$S$2:$ S$8000,0))

--
Kevin Vaughn


"mgarcia" wrote:


I have 4 drivers each with a user ID unique to them (ex: AA12345) each
of these drivers does a work function of inventoring items which shows
as either "INVEN01" or "INVEN02" on my report. "J" column for ID and
"M" column for function code, then in column "S" is the time spent on
that function. I would like to create a formula to Sum the time for
each driver on function INVEN01 and INVEN02. I tired coming up with
something for just for INVEN01 first - figuring I could do the same for
INVEN02 and add them together. I got this:

SUM(IF((YMS_TEST.xls!$J$2:$J$8000=AA12345)+(YMS_TE ST.xls!$M$2:$M$8000="INVEN01"),YMS_TEST.xls!$S$2:$ S$8000,0))

YMS_Test.XLS is the file my report comes out in. B4 refers to the
drivers ID. I thought this would reference the file and only sum the
time for rows were the ID and function were AA12345+INVEN01, but
instead I got rows added were the ID matched plus rows were the
function matched. Is there a way to make the system check both?

Thanks!


--
mgarcia
------------------------------------------------------------------------
mgarcia's Profile: http://www.excelforum.com/member.php...o&userid=30584
View this thread: http://www.excelforum.com/showthread...hreadid=509188

Thanks for the help! I worked on this for a couple horus last night and
figured out the "+" was incorrect. The "*" did the trick.

Thanks!


--
mgarcia
------------------------------------------------------------------------
mgarcia's Profile: http://www.excelforum.com/member.php...o&userid=30584
View this thread: http://www.excelforum.com/showthread...hreadid=509188

Glad you got it working.
--
Kevin Vaughn


"mgarcia" wrote:


Thanks for the help! I worked on this for a couple horus last night and
figured out the "+" was incorrect. The "*" did the trick.

Thanks!


--
mgarcia
------------------------------------------------------------------------
mgarcia's Profile: http://www.excelforum.com/member.php...o&userid=30584
View this thread: http://www.excelforum.com/showthread...hreadid=509188