Need to Sum when THREE Conditions are met
 

I need to sum a Column when it meets Three conditions.

When the date is the same as "a189", when there is a value in a cell in
Column "C" and there is also a value in column "O" (both the values in
column "C" & "O" have to be in the same row. If TRUE, give a value of "1" at
each occurrence and sum those values. I came up with the following:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189)*(LEFT('Service
Request Log'!$C$4:$C$5000,1)="0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A189)*(LEFT('Service Request Log'!$O$4:$O$5000,1)<0))

But this is treating each column ("C" & "O") as separate values and adding
them together. So instead of a value of say, "5" for a certain date, I am
getting "15" because there are "10" entries in column "C" and "5" entries in
column "O".

I probably should mentione that Column "C" has a string of numbers (IE:
06-123.123) and column "O" will just have numbers...no text in either one so
I guess I should not be using the LEFT function, huh?

Need to Sum when THREE Conditions are met
 

Perhaps
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189), --('Service
Request Log'!$C$4:$C$5000)<""), --('Service Request Log'!$O$4:$O$5000<""))



"jimswinder" wrote:

I need to sum a Column when it meets Three conditions.

When the date is the same as "a189", when there is a value in a cell in
Column "C" and there is also a value in column "O" (both the values in
column "C" & "O" have to be in the same row. If TRUE, give a value of "1" at
each occurrence and sum those values. I came up with the following:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189)*(LEFT('Service
Request Log'!$C$4:$C$5000,1)="0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A189)*(LEFT('Service Request Log'!$O$4:$O$5000,1)<0))

But this is treating each column ("C" & "O") as separate values and adding
them together. So instead of a value of say, "5" for a certain date, I am
getting "15" because there are "10" entries in column "C" and "5" entries in
column "O".

I probably should mentione that Column "C" has a string of numbers (IE:
06-123.123) and column "O" will just have numbers...no text in either one so
I guess I should not be using the LEFT function, huh?

Need to Sum when THREE Conditions are met
 

When I use:
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189),--('Service Request
Log'!$C$4:$C$5000)<"")--('Service Request Log'!$O$4:$O$5000<"")

It comes back as #VALUE!.

"JMB" wrote:

Perhaps
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189), --('Service
Request Log'!$C$4:$C$5000)<""), --('Service Request Log'!$O$4:$O$5000<""))



"jimswinder" wrote:

I need to sum a Column when it meets Three conditions.

When the date is the same as "a189", when there is a value in a cell in
Column "C" and there is also a value in column "O" (both the values in
column "C" & "O" have to be in the same row. If TRUE, give a value of "1" at
each occurrence and sum those values. I came up with the following:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189)*(LEFT('Service
Request Log'!$C$4:$C$5000,1)="0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A189)*(LEFT('Service Request Log'!$O$4:$O$5000,1)<0))

But this is treating each column ("C" & "O") as separate values and adding
them together. So instead of a value of say, "5" for a certain date, I am
getting "15" because there are "10" entries in column "C" and "5" entries in
column "O".

I probably should mentione that Column "C" has a string of numbers (IE:
06-123.123) and column "O" will just have numbers...no text in either one so
I guess I should not be using the LEFT function, huh?

Need to Sum when THREE Conditions are met
 

That formula is not the same. You are missing a comma and end parentheses.

"jimswinder" wrote:

When I use:
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189),--('Service Request
Log'!$C$4:$C$5000)<"")--('Service Request Log'!$O$4:$O$5000<"")

It comes back as #VALUE!.

"JMB" wrote:

Perhaps
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189), --('Service
Request Log'!$C$4:$C$5000)<""), --('Service Request Log'!$O$4:$O$5000<""))



"jimswinder" wrote:

I need to sum a Column when it meets Three conditions.

When the date is the same as "a189", when there is a value in a cell in
Column "C" and there is also a value in column "O" (both the values in
column "C" & "O" have to be in the same row. If TRUE, give a value of "1" at
each occurrence and sum those values. I came up with the following:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189)*(LEFT('Service
Request Log'!$C$4:$C$5000,1)="0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A189)*(LEFT('Service Request Log'!$O$4:$O$5000,1)<0))

But this is treating each column ("C" & "O") as separate values and adding
them together. So instead of a value of say, "5" for a certain date, I am
getting "15" because there are "10" entries in column "C" and "5" entries in
column "O".

I probably should mentione that Column "C" has a string of numbers (IE:
06-123.123) and column "O" will just have numbers...no text in either one so
I guess I should not be using the LEFT function, huh?

Need to Sum when THREE Conditions are met
 

hmmmmm...I thought I copied and pasted...will try again.

"JMB" wrote:

That formula is not the same. You are missing a comma and end parentheses.

"jimswinder" wrote:

When I use:
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189),--('Service Request
Log'!$C$4:$C$5000)<"")--('Service Request Log'!$O$4:$O$5000<"")

It comes back as #VALUE!.

"JMB" wrote:

Perhaps
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189), --('Service
Request Log'!$C$4:$C$5000)<""), --('Service Request Log'!$O$4:$O$5000<""))



"jimswinder" wrote:

I need to sum a Column when it meets Three conditions.

When the date is the same as "a189", when there is a value in a cell in
Column "C" and there is also a value in column "O" (both the values in
column "C" & "O" have to be in the same row. If TRUE, give a value of "1" at
each occurrence and sum those values. I came up with the following:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189)*(LEFT('Service
Request Log'!$C$4:$C$5000,1)="0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A189)*(LEFT('Service Request Log'!$O$4:$O$5000,1)<0))

But this is treating each column ("C" & "O") as separate values and adding
them together. So instead of a value of say, "5" for a certain date, I am
getting "15" because there are "10" entries in column "C" and "5" entries in
column "O".

I probably should mentione that Column "C" has a string of numbers (IE:
06-123.123) and column "O" will just have numbers...no text in either one so
I guess I should not be using the LEFT function, huh?

Need to Sum when THREE Conditions are met
 

I see what happened...Excel says it finds a problem with the equation and do
I want to acccept the proposed new formula.
And the new formula is what I pasted below.

"JMB" wrote:

That formula is not the same. You are missing a comma and end parentheses.

"jimswinder" wrote:

When I use:
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189),--('Service Request
Log'!$C$4:$C$5000)<"")--('Service Request Log'!$O$4:$O$5000<"")

It comes back as #VALUE!.

"JMB" wrote:

Perhaps
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189), --('Service
Request Log'!$C$4:$C$5000)<""), --('Service Request Log'!$O$4:$O$5000<""))



"jimswinder" wrote:

I need to sum a Column when it meets Three conditions.

When the date is the same as "a189", when there is a value in a cell in
Column "C" and there is also a value in column "O" (both the values in
column "C" & "O" have to be in the same row. If TRUE, give a value of "1" at
each occurrence and sum those values. I came up with the following:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189)*(LEFT('Service
Request Log'!$C$4:$C$5000,1)="0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A189)*(LEFT('Service Request Log'!$O$4:$O$5000,1)<0))

But this is treating each column ("C" & "O") as separate values and adding
them together. So instead of a value of say, "5" for a certain date, I am
getting "15" because there are "10" entries in column "C" and "5" entries in
column "O".

I probably should mentione that Column "C" has a string of numbers (IE:
06-123.123) and column "O" will just have numbers...no text in either one so
I guess I should not be using the LEFT function, huh?

Need to Sum when THREE Conditions are met
 

Got it. I had an extra parantheses

Corrected:
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189), --('Service
Request Log'!$C$4:$C$5000<""), --('Service Request Log'!$O$4:$O$5000<""))

"jimswinder" wrote:

I see what happened...Excel says it finds a problem with the equation and do
I want to acccept the proposed new formula.
And the new formula is what I pasted below.

"JMB" wrote:

That formula is not the same. You are missing a comma and end parentheses.

"jimswinder" wrote:

When I use:
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189),--('Service Request
Log'!$C$4:$C$5000)<"")--('Service Request Log'!$O$4:$O$5000<"")

It comes back as #VALUE!.

"JMB" wrote:

Perhaps
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189), --('Service
Request Log'!$C$4:$C$5000)<""), --('Service Request Log'!$O$4:$O$5000<""))



"jimswinder" wrote:

I need to sum a Column when it meets Three conditions.

When the date is the same as "a189", when there is a value in a cell in
Column "C" and there is also a value in column "O" (both the values in
column "C" & "O" have to be in the same row. If TRUE, give a value of "1" at
each occurrence and sum those values. I came up with the following:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189)*(LEFT('Service
Request Log'!$C$4:$C$5000,1)="0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A189)*(LEFT('Service Request Log'!$O$4:$O$5000,1)<0))

But this is treating each column ("C" & "O") as separate values and adding
them together. So instead of a value of say, "5" for a certain date, I am
getting "15" because there are "10" entries in column "C" and "5" entries in
column "O".

I probably should mentione that Column "C" has a string of numbers (IE:
06-123.123) and column "O" will just have numbers...no text in either one so
I guess I should not be using the LEFT function, huh?

Need to Sum when THREE Conditions are met
 

Actually someone else found me an answer in the meantime....
=SUM(IF('Service Request Log'!$B$4:$B$5000=$A189,IF('Service Request
Log'!$C$4:$C$5000<"",IF('Service Request Log'!$O$4:$O$5000<"",1,0),0),0))

But I certainly appreciate you taking the time to try and help me. Your
equation seems a bit simpler, so I am going to try it.

"JMB" wrote:

Got it. I had an extra parantheses

Corrected:
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189), --('Service
Request Log'!$C$4:$C$5000<""), --('Service Request Log'!$O$4:$O$5000<""))

"jimswinder" wrote:

I see what happened...Excel says it finds a problem with the equation and do
I want to acccept the proposed new formula.
And the new formula is what I pasted below.

"JMB" wrote:

That formula is not the same. You are missing a comma and end parentheses.

"jimswinder" wrote:

When I use:
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189),--('Service Request
Log'!$C$4:$C$5000)<"")--('Service Request Log'!$O$4:$O$5000<"")

It comes back as #VALUE!.

"JMB" wrote:

Perhaps
=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189), --('Service
Request Log'!$C$4:$C$5000)<""), --('Service Request Log'!$O$4:$O$5000<""))



"jimswinder" wrote:

I need to sum a Column when it meets Three conditions.

When the date is the same as "a189", when there is a value in a cell in
Column "C" and there is also a value in column "O" (both the values in
column "C" & "O" have to be in the same row. If TRUE, give a value of "1" at
each occurrence and sum those values. I came up with the following:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A189)*(LEFT('Service
Request Log'!$C$4:$C$5000,1)="0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A189)*(LEFT('Service Request Log'!$O$4:$O$5000,1)<0))

But this is treating each column ("C" & "O") as separate values and adding
them together. So instead of a value of say, "5" for a certain date, I am
getting "15" because there are "10" entries in column "C" and "5" entries in
column "O".

I probably should mentione that Column "C" has a string of numbers (IE:
06-123.123) and column "O" will just have numbers...no text in either one so
I guess I should not be using the LEFT function, huh?