SUMPRODUCT AND LEFT

[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?

 

[Don Guillett]

66 41

 

[Pete_UK]

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

 

[K]

Thank you so much. Let me try and hope it works!

 

[K]

Worked like a charm. Thank you so much.

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

 

[Pete_UK]

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]

Thanks for that. I need to learn more about wildcards in general.