Sum when two conditions are met

[jimswinder]

Sorry to be such a bother to everyone...need one last formula.

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
occurence 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 seperate values and adding
them together. So instead of a value of say, "5" for a certain date, I am
getting "15" becasue there are "10" entries in column "C" and "5" entries in
column "O".

[jimswinder]

I probably should have mentioned 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 gues I should not be using the LEFT function, huh?

"jimswinder" wrote:

> Sorry to be such a bother to everyone...need one last formula.
>
> 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
> occurence 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 seperate values and adding
> them together. So instead of a value of say, "5" for a certain date, I am
> getting "15" becasue there are "10" entries in column "C" and "5" entries in
> column "O".

[JMB]

See response in your other post.


"jimswinder" wrote:

> I probably should have mentioned 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 gues I should not be using the LEFT function, huh?
>
> "jimswinder" wrote:
>
> > Sorry to be such a bother to everyone...need one last formula.
> >
> > 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
> > occurence 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 seperate values and adding
> > them together. So instead of a value of say, "5" for a certain date, I am
> > getting "15" becasue there are "10" entries in column "C" and "5" entries in
> > column "O".