Combo Sumproduct

Hey all,

This shouldn't be too difficult but I'm running out of time to figure this out.

Here is the current equation I have:

The idea is too add up the number of occurrences of "tuff" and "rouge" occurrences in column D that actually has a something in the F column. However, the tuff and rouge are the first word followed by numbers, such as Tuff23 or Rouge45. The wildcard number doesn't mean anything...just the first word. For example, in cell D9 "Tuff33" is displayed and a character is in cell F9. This will count as 1. If F9 was blank, then the result would still be zero.

=SUMPRODUCT((ISNUMBER(FIND("TUFF",$D$9:$D$50)))*($F$9:$F$50<>""))+SUMPRODUCT((ISNUMBER(FIND("ROUGE",$D$9:$D$50)))*($F$9:$F$50<>""))

I have two questions regarding this equation.

1. I would like to combine these two functions into one. I tried to use the AND with the FIND function but didn't work right...the way I did it tho.

2. Here is the main issue with this equation. The text MUST BE capital letters. What if Tuff21 was entered? It doesn't count it...it will only count if it is entered as TUFF21. How can I get it to work regardless of case?

I greatly appreciate your help with this.

DrV

I found one of my answers. Change the FIND function with SEARCH function. Now, I would like to combine them...any ideas. Thanks.

=SUMPRODUCT((LEFT(d9:d50,{4,5})={"TUFF","Rouge"})*(f9:f50<>""))


Regards

Dav

Got it...thanks so much all for your help.

Needed to bump this up for some more help. Below is the formula I've used; however, I need to do this for multiple sheets instead just one sheet.

=SUMPRODUCT((ISNUMBER(SEARCH({"Tuff","Rogue"},October!$D$9:$D$111)))*(October!$F$9:$F$111<>""))

I was able to add up all the occurrences of Tuff and Rogue while something is entered in the F column. However, I could add other sheets? I figured I need to put {October, November, December} statement somewhere but not sure. All the cells are the same; however, just need to add several more sheets (months). Greatly appreciate your expertise...you have all helped me greatly over the past 2 yrs with projects that have come my way. Thanks.

DrV

Originally Posted by drvortex

Hey all,

This shouldn't be too difficult but I'm running out of time to figure this out.

Here is the current equation I have:

The idea is too add up the number of occurrences of "tuff" and "rouge" occurrences in column D that actually has a something in the F column. However, the tuff and rouge are the first word followed by numbers, such as Tuff23 or Rouge45. The wildcard number doesn't mean anything...just the first word. For example, in cell D9 "Tuff33" is displayed and a character is in cell F9. This will count as 1. If F9 was blank, then the result would still be zero.

=SUMPRODUCT((ISNUMBER(FIND("TUFF",$D$9:$D$50)))*($F$9:$F$50<>""))+SUMPRODUCT((ISNUMBER(FIND("ROUGE",$D$9:$D$50)))*($F$9:$F$50<>""))

I have two questions regarding this equation.

1. I would like to combine these two functions into one. I tried to use the AND with the FIND function but didn't work right...the way I did it tho.

2. Here is the main issue with this equation. The text MUST BE capital letters. What if Tuff21 was entered? It doesn't count it...it will only count if it is entered as TUFF21. How can I get it to work regardless of case?

I greatly appreciate your help with this.

DrV

=SUMPRODUCT((ISNUMBER(SEARCH({"Tuff","Rouge"},D9:D50)))*(F9:F50<>""))
or
=SUMPRODUCT((LEFT(D9:D50,4)="Tuff")+(LEFT(D9:D50,5)="Rouge")*(F9:F50<>""))