Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct Help
Please help me with the sumproduct formula. I'm using this formula to tell me
how many attendees, by department, attended a class at a particular time, and this WORKS: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1) Now, I'm being asked to add the location, so I need to know how many attendees, by department, attended a class in San Jose and how many attended class in San Francisco, so I did added the location to the end: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1),(--(J3:J64=O1) and it's NOT working, I even typed in the location as (J3:j64="San Jose") and it still doesn't work. The formula worked for one department but not the others, for some reason it will not count the other departments -- it gives me 0 instead of the actual count. I checked to make sure the correct cells were typed in for each department and they are Can anyone tell me what I'm doing wrong? -- Thanks, Annie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct Help
Hi Annie
Nothing seems to be wrong with your formula, which is also confirmed as it works for one department. I think you have to look for leading or trailing spaces in the location column (and maybe in the others too). Hopes this helps. .... Per "Annie" skrev i meddelelsen ... Please help me with the sumproduct formula. I'm using this formula to tell me how many attendees, by department, attended a class at a particular time, and this WORKS: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1) Now, I'm being asked to add the location, so I need to know how many attendees, by department, attended a class in San Jose and how many attended class in San Francisco, so I did added the location to the end: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1),(--(J3:J64=O1) and it's NOT working, I even typed in the location as (J3:j64="San Jose") and it still doesn't work. The formula worked for one department but not the others, for some reason it will not count the other departments -- it gives me 0 instead of the actual count. I checked to make sure the correct cells were typed in for each department and they are Can anyone tell me what I'm doing wrong? -- Thanks, Annie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct Help
Hi Jessen,
It's not working. When I remove the location it works on the other departments, for some reason it doesn't like the location. Is there alternate formula I can use? -- Thanks, Annie "Per Jessen" wrote: Hi Annie Nothing seems to be wrong with your formula, which is also confirmed as it works for one department. I think you have to look for leading or trailing spaces in the location column (and maybe in the others too). Hopes this helps. .... Per "Annie" skrev i meddelelsen ... Please help me with the sumproduct formula. I'm using this formula to tell me how many attendees, by department, attended a class at a particular time, and this WORKS: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1) Now, I'm being asked to add the location, so I need to know how many attendees, by department, attended a class in San Jose and how many attended class in San Francisco, so I did added the location to the end: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1),(--(J3:J64=O1) and it's NOT working, I even typed in the location as (J3:j64="San Jose") and it still doesn't work. The formula worked for one department but not the others, for some reason it will not count the other departments -- it gives me 0 instead of the actual count. I checked to make sure the correct cells were typed in for each department and they are Can anyone tell me what I'm doing wrong? -- Thanks, Annie |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct Help
"Annie" wrote:
Can anyone tell me what I'm doing wrong? To begin with, you are not copy-and-pasting the formulas exactly as they appear in the worksheet (i.e. the Formula Bar). What you have typed in your posting has obvious typos. Perhaps even your actual formula has obvious typos. If you want to help us help you, you need to show us the actual formula. Second, assuming your formula is entered correctly as implied by what you wrote here, we might need to see exactly what is in O1 and at least some of J3:J64 that you believe should match. That is more difficult since copy-and-pasting text into a web page for posting does not always faithfully reproduce the text exactly. So some additional information might give credibility to the posted text. For example, =LEN(O1) and =LEN(x), where "x" are some matching J3:J64. Finally, we need some evidence that there are indeed some rows where all of the conditions are met. Provide an example, say row 32, where SUMPRODUCT(--(D32=N1),-(F32=N4),--(G32=M1)) is 1, but SUMPRODUCT(--(D32=N1),-(F32=N4),--(G32=M1),--(J32=O1)) is zero, but should also be 1. Provide LEN(J32) and LEN(O1), and copy-and-paste their cell contents from the Formula Bar. ----- original message ----- "Annie" wrote in message ... Please help me with the sumproduct formula. I'm using this formula to tell me how many attendees, by department, attended a class at a particular time, and this WORKS: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1) Now, I'm being asked to add the location, so I need to know how many attendees, by department, attended a class in San Jose and how many attended class in San Francisco, so I did added the location to the end: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1),(--(J3:J64=O1) and it's NOT working, I even typed in the location as (J3:j64="San Jose") and it still doesn't work. The formula worked for one department but not the others, for some reason it will not count the other departments -- it gives me 0 instead of the actual count. I checked to make sure the correct cells were typed in for each department and they are Can anyone tell me what I'm doing wrong? -- Thanks, Annie |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct Help
JoeU2004 & Per Jessen,
THANK YOU VERY MUCH!! I had to step away and come back to it. You guys were right. My formula is correct. While double checking my numbers, I counted incorrectly. Sorry for the bother. I spent to many hours looking at the data in one setting. I appreciate your help!! -- Thanks, Annie "JoeU2004" wrote: "Annie" wrote: Can anyone tell me what I'm doing wrong? To begin with, you are not copy-and-pasting the formulas exactly as they appear in the worksheet (i.e. the Formula Bar). What you have typed in your posting has obvious typos. Perhaps even your actual formula has obvious typos. If you want to help us help you, you need to show us the actual formula. Second, assuming your formula is entered correctly as implied by what you wrote here, we might need to see exactly what is in O1 and at least some of J3:J64 that you believe should match. That is more difficult since copy-and-pasting text into a web page for posting does not always faithfully reproduce the text exactly. So some additional information might give credibility to the posted text. For example, =LEN(O1) and =LEN(x), where "x" are some matching J3:J64. Finally, we need some evidence that there are indeed some rows where all of the conditions are met. Provide an example, say row 32, where SUMPRODUCT(--(D32=N1),-(F32=N4),--(G32=M1)) is 1, but SUMPRODUCT(--(D32=N1),-(F32=N4),--(G32=M1),--(J32=O1)) is zero, but should also be 1. Provide LEN(J32) and LEN(O1), and copy-and-paste their cell contents from the Formula Bar. ----- original message ----- "Annie" wrote in message ... Please help me with the sumproduct formula. I'm using this formula to tell me how many attendees, by department, attended a class at a particular time, and this WORKS: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1) Now, I'm being asked to add the location, so I need to know how many attendees, by department, attended a class in San Jose and how many attended class in San Francisco, so I did added the location to the end: =sumproduct(--(D3:D64=N1),--(F3:F64=N4),--(G3:G64=M1),(--(J3:J64=O1) and it's NOT working, I even typed in the location as (J3:j64="San Jose") and it still doesn't work. The formula worked for one department but not the others, for some reason it will not count the other departments -- it gives me 0 instead of the actual count. I checked to make sure the correct cells were typed in for each department and they are Can anyone tell me what I'm doing wrong? -- Thanks, Annie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions |