=SUMIF(Rewards!$AH$3:$BI$3,Targets!O30, Rewards!$AH$20:$BI$20)
=SUMIF(Rewards!$AH$3:$BI$3,Targets!O30, Rewards!$AH$20:$BI$20)
In the above equation I am trying to get a total using sumif.
The rewards cells row 3 refer to a range of automatically generated
consecutive date cells.

The target cell is a manually entered date

The reward cells in row 20 are automatically generated numbers.

What I am trying to do is have the calculation check for the date in
Targets and then only sum the numbers that are entered on dates equal
or after that entered in targets
I also tried
=SUMIF(Rewards!$AH$3:$BI$3, "Targets!O30:<O31",
Rewards!$AH$20:$BI$20)

in the hope of getting it to sum between two given dates.
Any clues out there? I'm going around in circles here.


--
dond
------------------------------------------------------------------------
dond's Profile: http://www.excelforum.com/member.php...o&userid=25477
View this thread: http://www.excelforum.com/showthread...hreadid=483373

This one always gets me and I can never remember how to do it, so I had a
friend write up this article, Dond:
http://www.officearticles.com/excel/...soft_excel.htm
************
Anne Troy
www.OfficeArticles.com

"dond" wrote in message
...

=SUMIF(Rewards!$AH$3:$BI$3,Targets!O30, Rewards!$AH$20:$BI$20)
=SUMIF(Rewards!$AH$3:$BI$3,Targets!O30, Rewards!$AH$20:$BI$20)
In the above equation I am trying to get a total using sumif.
The rewards cells row 3 refer to a range of automatically generated
consecutive date cells.

The target cell is a manually entered date

The reward cells in row 20 are automatically generated numbers.

What I am trying to do is have the calculation check for the date in
Targets and then only sum the numbers that are entered on dates equal
or after that entered in targets
I also tried
=SUMIF(Rewards!$AH$3:$BI$3, "Targets!O30:<O31",
Rewards!$AH$20:$BI$20)

in the hope of getting it to sum between two given dates.
Any clues out there? I'm going around in circles here.


--
dond
------------------------------------------------------------------------
dond's Profile:
http://www.excelforum.com/member.php...o&userid=25477
View this thread: http://www.excelforum.com/showthread...hreadid=483373

Hi everyone,

Could anyone help!!!

I would like to know if I could use SUMIF function with two criteria
and two ranges in it. For example, I would like to sum up a columb if
any given cell in that columb fulfills two criteria. The one criteria
is in one columb, the other criteria is in other columb.

Thank you.


--
kras
------------------------------------------------------------------------
kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
View this thread: http://www.excelforum.com/showthread...hreadid=483373

Not =sumif().

But you can use =sumproduct()

=sumproduct(--(a1:a10="something"),--(b1:b10="another"),c1:c10)

=sumproduct() likes to work with numbers. The -- converts true/falses to 1/0's.

Extend your range to what you want, but don't use the whole column.

kras wrote:

Hi everyone,

Could anyone help!!!

I would like to know if I could use SUMIF function with two criteria
and two ranges in it. For example, I would like to sum up a columb if
any given cell in that columb fulfills two criteria. The one criteria
is in one columb, the other criteria is in other columb.

Thank you.

--
kras
------------------------------------------------------------------------
kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
View this thread: http://www.excelforum.com/showthread...hreadid=483373

--

Dave Peterson

Hi Dave,

Thank you for your reply.

The problem is that one of the columbs contains letters, not numbers
and sumproduct formula returns #VALUE!

Probably I have to be more specific. I'll give you an example. So,
there are three columbs in my spreadsheet. The first contains time in
which a process starts /for ex. 11:24:32/. The second columb contains
the duration of the process /for ex. 00:03:45/. The third columb
contains the kind of process/ for example SB, SS, etc./.

I would like to find out the average time duration of a given process
/lets say SB/ after 15:30:00.


--
kras
------------------------------------------------------------------------
kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
View this thread: http://www.excelforum.com/showthread...hreadid=483373

This formula:

=sumproduct(--(a1:a10="something"),--(b1:b10="another"),c1:c10)

expects to find numbers in C1:C10. If you have text/errors in that range, then
you'll have trouble.

(You'd have to adjust the ranges to match your data.)

But from what you wrote in your followup, you're only looking at one criteria.

So maybe something like:
=SUMIF(C1:C10,"SB",B1:B10)/COUNTIF(C1:C10,"SB")

Oopsie....

You wanted it after 15:30:00, so back to =sumproduct()

=sumproduct(--(a1:a10=time(15,30,0)),--(c1:c10="sb"),b1:b10)
/ sumproduct(--(a1:a10=time(15,30,0)),--(c1:c10="sb"))

All one cell.

That assumes that the times in column A are really times--they don't include
dates.

(Sum up all the duration times in column B that matches the criteria and divide
it by the count that match both criteria.)



kras wrote:

Hi Dave,

Thank you for your reply.

The problem is that one of the columbs contains letters, not numbers
and sumproduct formula returns #VALUE!

Probably I have to be more specific. I'll give you an example. So,
there are three columbs in my spreadsheet. The first contains time in
which a process starts /for ex. 11:24:32/. The second columb contains
the duration of the process /for ex. 00:03:45/. The third columb
contains the kind of process/ for example SB, SS, etc./.

I would like to find out the average time duration of a given process
/lets say SB/ after 15:30:00.

--
kras
------------------------------------------------------------------------
kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
View this thread: http://www.excelforum.com/showthread...hreadid=483373

--

Dave Peterson