SUMPRODUCT AND LEFT

  • Thread starter Thread starter K
  • Start date Start date
K

K

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?
 
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("RA",'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
 
Worked like a charm. Thank you so much.

Pete_UK said:
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("RA",'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
Click to expand...
 
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
 
Back
Top