=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

Hi Anne,
Really appreciate the assistance. I would never have got that
calculation and it is a very useful tip to file away for later
reference. This completed the last part of a project I have been
working on for months. Once again thankyou for the help.
Dond


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

Hi Dave,

Thank you again for your prompt answer.

I tried the formula you wrote down in your last post. The problem is
that as soon as i write a condition such as =time(15;30;0) or ="sb" in
the formula, it returns 0. If I remove conditions from the formula it
returns a result.
Also, I looked at formula help menu. It is said there that if one of
the arrays are not numeric the formula considers them as zeros. I think
this is an additional problem for using this formula, because one of
arrays are letter /ex. SB/.

Do you think I could use another formula, instead of SUMPRODUCT?

In my case, lets say, I need two levels of data filtering. The first
level is to filter data by the condition "SB", and the second level is
to filter the already filtered data by the condition "time". After that
I have to find the average minutes a process lasts after a specific
time, say 15:30:00.
For example:

Time In duration process
10:23:12 2:45 SB
10:45:05 1:12 AA
10:56:25 0:56 SB
15:45:14 5:23 SB

What if I use IF functions? Can I manage this in one cell?

Best regards,
kras




Dave Peterson Wrote:
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
------------------------------------------------------------------------
kras's Profile: http://www.excelforum.com/member.php...o&userid=28667
View this thread: http://www.excelforum.com/showthread...hreadid=483373

Change the formula to look at just one row that should work ok.

Now look at the values in the cell (leading/trailing spaces in the SB column) or
dates/times in the time column.

In fact, make sure that the time entered is really a time.

Format that cell as general and what do you see. It sould be a decimal number
between 0 and 1.

If that doesn't work, then post the single row of data and the current formula
you're trying.


kras wrote:

Hi Dave,

Thank you again for your prompt answer.

I tried the formula you wrote down in your last post. The problem is
that as soon as i write a condition such as =time(15;30;0) or ="sb" in
the formula, it returns 0. If I remove conditions from the formula it
returns a result.
Also, I looked at formula help menu. It is said there that if one of
the arrays are not numeric the formula considers them as zeros. I think
this is an additional problem for using this formula, because one of
arrays are letter /ex. SB/.

Do you think I could use another formula, instead of SUMPRODUCT?

In my case, lets say, I need two levels of data filtering. The first
level is to filter data by the condition "SB", and the second level is
to filter the already filtered data by the condition "time". After that
I have to find the average minutes a process lasts after a specific
time, say 15:30:00.
For example:

Time In duration process
10:23:12 2:45 SB
10:45:05 1:12 AA
10:56:25 0:56 SB
15:45:14 5:23 SB

What if I use IF functions? Can I manage this in one cell?

Best regards,
kras

Dave Peterson Wrote:
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
------------------------------------------------------------------------
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,

I didn't manage to do it. I attached an excel file with the example
table and the formula you provided.


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4036 |
+-------------------------------------------------------------------+

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

I don't connect through excelforum--so I can't see the attachments.

But I'm one of those people who won't open attachments.

Maybe someone else will open it and jump in with an answer--or you can post in
plain text.

kras wrote:

Hi Dave,

I didn't manage to do it. I attached an excel file with the example
table and the formula you provided.

+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4036 |
+-------------------------------------------------------------------+

--
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

Sure Dave,

I'm posting a plain text below:

ColumbA/ColumbB/ColumbC/ColumbD
Time In Time out Duration Process
10:43:36 10:45:49 02:13 SB
10:50:34 10:50:52 00:18 BB
15:40:44 15:43:08 02:24 SB
15:48:25 15:49:00 00:35 SB
11:27:00 11:28:14 01:14 BB

Single row formula: =SUMPRODUCT((A4=TIME(15;30;0));(D4="SB");C4). I
didn't divided it to SUMPRODUCT((A4=TIME(15;30;0));(D4="SB"))
purposely and saw that it returns #VALUE!

Single row formula:
=SUMPRODUCT((A4=TIME(15;30;0));(D4="SB");C4)/SUMPRODUCT((A4=TIME(15;30;0));(D4="SB"))
It returns #VALUE!

The Time In and Time Out cells are set to: hh:mm:ss.
The Duration cells are set to: mm:ss
The Process cell is set to: general
Formula cell is set to: general


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

You seem to have dropped some double negative signs.

This worked with my USA settings (comma vs. semicolon)

=SUMPRODUCT(--(A4:A99=TIME(15,30,0)),--(D4:D99="SB"),C4:C99)
/SUMPRODUCT(--(A4:A99=TIME(15,30,0)),--(D4:D99="SB"))

Watchout for typos:

=SUMPRODUCT(--(A4:A99=TIME(15;30;0));--(D4:D99="SB");C4:C99)
/SUMPRODUCT(--(A4:A99=TIME(15;30;0));--(D4:D99="SB"))


=sumproduct() likes to work with numbers. The -- stuff converts true and false
to 1 and 0.


kras wrote:

Sure Dave,

I'm posting a plain text below:

ColumbA/ColumbB/ColumbC/ColumbD
Time In Time out Duration Process
10:43:36 10:45:49 02:13 SB
10:50:34 10:50:52 00:18 BB
15:40:44 15:43:08 02:24 SB
15:48:25 15:49:00 00:35 SB
11:27:00 11:28:14 01:14 BB

Single row formula: =SUMPRODUCT((A4=TIME(15;30;0));(D4="SB");C4). I
didn't divided it to SUMPRODUCT((A4=TIME(15;30;0));(D4="SB"))
purposely and saw that it returns #VALUE!

Single row formula:
=SUMPRODUCT((A4=TIME(15;30;0));(D4="SB");C4)/SUMPRODUCT((A4=TIME(15;30;0));(D4="SB"))
It returns #VALUE!

The Time In and Time Out cells are set to: hh:mm:ss.
The Duration cells are set to: mm:ss
The Process cell is set to: general
Formula cell is set to: general

--
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,

Wow!!! It works now. Thank you very much for your precious help. I
really appreciate it.

After using this formula it saved me a lot of redundant columbs.


Best regards,
kras


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