| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT AND LEFT
I'm trying to get this formula to work but it's giving a #NA value. The
formula works fine until I add the last condition with the LEFT function. =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(LEFT('CPO Tracking Log'!$C$14:$C$66,2)="RA")) What I would ideally like to do is to use a wildcard function for the last condition, so that if the cell contains RA in the begining or end then sum it. HELP? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT AND LEFT
66 41
-- Don Guillett Microsoft MVP Excel SalesAid Software "K" wrote in message ... I'm trying to get this formula to work but it's giving a #NA value. The formula works fine until I add the last condition with the LEFT function. =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(LEFT('CPO Tracking Log'!$C$14:$C$66,2)="RA")) What I would ideally like to do is to use a wildcard function for the last condition, so that if the cell contains RA in the begining or end then sum it. HELP? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT AND LEFT
You could try it like this:
=SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(ISNUMBER(SEARCH("R A",'CPO Tracking Log'!$C$14:$C$41)))) Note the ranges should be equal - you had C$66 in your last term, which was causing the error. This will count the cell if it contains "ra". If case is important to you, then use FIND instead of SEARCH. Hope this helps. Pete "K" wrote in message ... I'm trying to get this formula to work but it's giving a #NA value. The formula works fine until I add the last condition with the LEFT function. =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(LEFT('CPO Tracking Log'!$C$14:$C$66,2)="RA")) What I would ideally like to do is to use a wildcard function for the last condition, so that if the cell contains RA in the begining or end then sum it. HELP? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT AND LEFT
Thank you so much. Let me try and hope it works!
"Pete_UK" wrote: You could try it like this: =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(ISNUMBER(SEARCH("R A",'CPO Tracking Log'!$C$14:$C$41)))) Note the ranges should be equal - you had C$66 in your last term, which was causing the error. This will count the cell if it contains "ra". If case is important to you, then use FIND instead of SEARCH. Hope this helps. Pete "K" wrote in message ... I'm trying to get this formula to work but it's giving a #NA value. The formula works fine until I add the last condition with the LEFT function. =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(LEFT('CPO Tracking Log'!$C$14:$C$66,2)="RA")) What I would ideally like to do is to use a wildcard function for the last condition, so that if the cell contains RA in the begining or end then sum it. HELP? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT AND LEFT
Worked like a charm. Thank you so much.
"Pete_UK" wrote: You could try it like this: =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(ISNUMBER(SEARCH("R A",'CPO Tracking Log'!$C$14:$C$41)))) Note the ranges should be equal - you had C$66 in your last term, which was causing the error. This will count the cell if it contains "ra". If case is important to you, then use FIND instead of SEARCH. Hope this helps. Pete "K" wrote in message ... I'm trying to get this formula to work but it's giving a #NA value. The formula works fine until I add the last condition with the LEFT function. =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(LEFT('CPO Tracking Log'!$C$14:$C$66,2)="RA")) What I would ideally like to do is to use a wildcard function for the last condition, so that if the cell contains RA in the begining or end then sum it. HELP? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT AND LEFT
You're welcome - thanks for feeding back.
Note that with SEARCH you can include wildcard characters, so if you had "?RA" in the formula this would count "Crash" and "Trash" etc, but not "Rash". Pete "K" wrote in message ... Worked like a charm. Thank you so much. "Pete_UK" wrote: You could try it like this: =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(ISNUMBER(SEARCH("R A",'CPO Tracking Log'!$C$14:$C$41)))) Note the ranges should be equal - you had C$66 in your last term, which was causing the error. This will count the cell if it contains "ra". If case is important to you, then use FIND instead of SEARCH. Hope this helps. Pete "K" wrote in message ... I'm trying to get this formula to work but it's giving a #NA value. The formula works fine until I add the last condition with the LEFT function. =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(LEFT('CPO Tracking Log'!$C$14:$C$66,2)="RA")) What I would ideally like to do is to use a wildcard function for the last condition, so that if the cell contains RA in the begining or end then sum it. HELP? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT AND LEFT
Thanks for that. I need to learn more about wildcards in general.
"Pete_UK" wrote: You're welcome - thanks for feeding back. Note that with SEARCH you can include wildcard characters, so if you had "?RA" in the formula this would count "Crash" and "Trash" etc, but not "Rash". Pete "K" wrote in message ... Worked like a charm. Thank you so much. "Pete_UK" wrote: You could try it like this: =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(ISNUMBER(SEARCH("R A",'CPO Tracking Log'!$C$14:$C$41)))) Note the ranges should be equal - you had C$66 in your last term, which was causing the error. This will count the cell if it contains "ra". If case is important to you, then use FIND instead of SEARCH. Hope this helps. Pete "K" wrote in message ... I'm trying to get this formula to work but it's giving a #NA value. The formula works fine until I add the last condition with the LEFT function. =SUMPRODUCT(('CPO Tracking Log'!Q14:Q41=DATE(2008,6,1))*('CPO Tracking Log'!Q14:Q41<=DATE(2008,6,30))*(LEFT('CPO Tracking Log'!$C$14:$C$66,2)="RA")) What I would ideally like to do is to use a wildcard function for the last condition, so that if the cell contains RA in the begining or end then sum it. HELP? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to make left side stay still and right side can move left to r | Excel Discussion (Misc queries) | |||
| My Excel view is Right to Left instead of Left to Right !!! | Excel Discussion (Misc queries) | |||
| Column labels run right to left, not left to right | Excel Discussion (Misc queries) | |||
| Left and Sumproduct | Excel Discussion (Misc queries) | |||
| How to change the right-to-left worksheet to left-to-right workshe | Excel Discussion (Misc queries) | |||
Hybrid Mode
