| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Checking if 2 of 3 conditions are met... | Excel Discussion (Misc queries) | |||
| Count using 2 conditions, one of which being a "less than or equal to" - URGENT | Excel Discussion (Misc queries) | |||
| COUNT using multiple conditions | Excel Discussion (Misc queries) | |||
| More than 3 Conditional Formatting Conditions | Excel Worksheet Functions | |||
| How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Hybrid Mode
