Excel 2007 : LEFT With SUMPRODUCT

**erik.kirby** · 02-10-2011, 02:38 PM

I found this formula on this website, without the wildcard, and I kept getting zeros. I finally discovered that wildcards do not work with SUMPRODUCT. I cannot use the entire text from the PMARS tab because each comment has a different date. I found suggestions using LEFT() but do not understand where to place it? Any help is greatly appreciated.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(PMARS!Q2:Q100,ROW(PMARS!Q2:Q100)-ROW(PMARS!Q2),0,1)),--(PMARS!Q2:Q100="1st U Letter*"))

**NBVC** · 02-10-2011, 02:40 PM

Try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(PMARS!Q2:Q100,ROW(PMARS!Q2:Q100)-ROW(PMARS!Q2),0,1)),--(LEFT(PMARS!Q2:Q100,12)="1st U Letter"))

**daddylonglegs** · 02-10-2011, 02:43 PM

This formula will count visible values (after filtering) that begin with "U", is that the requirement?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(PMARS!Q2:Q100,ROW(PMARS!Q2:Q100)-ROW(PMARS!Q2),0,1)),--(LEFT(PMARS!Q2:Q100,1)="U"))

**NBVC** · 02-10-2011, 02:46 PM

Originally Posted by daddylonglegs

This formula will count visible values (after filtering) that begin with "U", is that the requirement?

Ha, Ha, I didn't even read that far into it...to realize that they are probably only looking for specifically the first letter... I was too busy counting characters.

**erik.kirby** · 02-10-2011, 03:00 PM

I was looking for it to count entire words but I think this will work. At first glance, the various comments I am counting all start with different letters. Thanks, you saved me a lot of time!