| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help With Formula
I am trying to use this formula to sum/count from another worksheet. What am
I missing? I keep getting a FALSE answer instead of numbers. =SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
You might try:
=SUMPRODUCT(--(Overall!O31:O1026=MONTH(C28)), --ISNUMBER(SEARCH(A35,O31:O1026))) In article , "roy.okinawa" wrote: I am trying to use this formula to sum/count from another worksheet. What am I missing? I keep getting a FALSE answer instead of numbers. =SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026))) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
You dropped a comma between arguments. You dropped the --() when you were
checking the month. And I think the rest depends on what's in your data. If Overall!o31:o1026 contain dates, then you're missing month() surrounding that range. =SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)), --(ISNUMBER(SEARCH(A35,O31:O1026)))) (all one cell) Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the formula in that last portion? But if that's true, then I'm not sure what you want to do with that last argument. You may want to describe what you're doing in words. roy.okinawa wrote: I am trying to use this formula to sum/count from another worksheet. What am I missing? I keep getting a FALSE answer instead of numbers. =SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026))) -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
Thanks.
The last portion of the argument looks at the word in cell A35 and finds all cells between O31:O1026 that contain that word on the overall worksheet. Then I get my total. So I probably need something in the last portion as well that looks at the Overall worksheet. "Dave Peterson" wrote: You dropped a comma between arguments. You dropped the --() when you were checking the month. And I think the rest depends on what's in your data. If Overall!o31:o1026 contain dates, then you're missing month() surrounding that range. =SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)), --(ISNUMBER(SEARCH(A35,O31:O1026)))) (all one cell) Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the formula in that last portion? But if that's true, then I'm not sure what you want to do with that last argument. You may want to describe what you're doing in words. roy.okinawa wrote: I am trying to use this formula to sum/count from another worksheet. What am I missing? I keep getting a FALSE answer instead of numbers. =SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026))) -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
How can something in Overall!o31:o1026 contain both a date (so you can check the
month) and a string? And even if this would work--and I don't think it will do what you want, you'd need something like: =SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)), --(ISNUMBER(SEARCH(A35,overall!O31:O1026)))) roy.okinawa wrote: Thanks. The last portion of the argument looks at the word in cell A35 and finds all cells between O31:O1026 that contain that word on the overall worksheet. Then I get my total. So I probably need something in the last portion as well that looks at the Overall worksheet. "Dave Peterson" wrote: You dropped a comma between arguments. You dropped the --() when you were checking the month. And I think the rest depends on what's in your data. If Overall!o31:o1026 contain dates, then you're missing month() surrounding that range. =SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)), --(ISNUMBER(SEARCH(A35,O31:O1026)))) (all one cell) Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the formula in that last portion? But if that's true, then I'm not sure what you want to do with that last argument. You may want to describe what you're doing in words. roy.okinawa wrote: I am trying to use this formula to sum/count from another worksheet. What am I missing? I keep getting a FALSE answer instead of numbers. =SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026))) -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
Thanks for correction. The last portion is not O31:O1026 it is S26:S1026. I
did a cut/paste and didn't update that part. The formula you provided works fine. "Dave Peterson" wrote: How can something in Overall!o31:o1026 contain both a date (so you can check the month) and a string? And even if this would work--and I don't think it will do what you want, you'd need something like: =SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)), --(ISNUMBER(SEARCH(A35,overall!O31:O1026)))) roy.okinawa wrote: Thanks. The last portion of the argument looks at the word in cell A35 and finds all cells between O31:O1026 that contain that word on the overall worksheet. Then I get my total. So I probably need something in the last portion as well that looks at the Overall worksheet. "Dave Peterson" wrote: You dropped a comma between arguments. You dropped the --() when you were checking the month. And I think the rest depends on what's in your data. If Overall!o31:o1026 contain dates, then you're missing month() surrounding that range. =SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)), --(ISNUMBER(SEARCH(A35,O31:O1026)))) (all one cell) Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the formula in that last portion? But if that's true, then I'm not sure what you want to do with that last argument. You may want to describe what you're doing in words. roy.okinawa wrote: I am trying to use this formula to sum/count from another worksheet. What am I missing? I keep getting a FALSE answer instead of numbers. =SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026))) -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help With Formula
Glad you got it working.
roy.okinawa wrote: Thanks for correction. The last portion is not O31:O1026 it is S26:S1026. I did a cut/paste and didn't update that part. The formula you provided works fine. "Dave Peterson" wrote: How can something in Overall!o31:o1026 contain both a date (so you can check the month) and a string? And even if this would work--and I don't think it will do what you want, you'd need something like: =SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)), --(ISNUMBER(SEARCH(A35,overall!O31:O1026)))) roy.okinawa wrote: Thanks. The last portion of the argument looks at the word in cell A35 and finds all cells between O31:O1026 that contain that word on the overall worksheet. Then I get my total. So I probably need something in the last portion as well that looks at the Overall worksheet. "Dave Peterson" wrote: You dropped a comma between arguments. You dropped the --() when you were checking the month. And I think the rest depends on what's in your data. If Overall!o31:o1026 contain dates, then you're missing month() surrounding that range. =SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)), --(ISNUMBER(SEARCH(A35,O31:O1026)))) (all one cell) Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the formula in that last portion? But if that's true, then I'm not sure what you want to do with that last argument. You may want to describe what you're doing in words. roy.okinawa wrote: I am trying to use this formula to sum/count from another worksheet. What am I missing? I keep getting a FALSE answer instead of numbers. =SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026))) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula? | Excel Worksheet Functions | |||
| Formula works in some cells, doesn't in other | New Users to Excel | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
