Looking for a wildcard in a SUMPRODUCT function

[lcmerten]

Good Afternoon!

So - I have two columns of data. Column F contains a regions (Africa, Middle East, Europe or UK) and column G contains a month and date range of an event occuring within that region, hoever not all cells in column G contain data. I would like to create a formula that will count the number of occurances of, for example, "Africa" in column F only if there is a value (any value) in the corresponding row of column G. The formula I have so far is this:

=SUMPRODUCT(($F$3:$F$271="Africa")*($G$3:$G$271="*"))

With "*" being intended as a wildcard (as it works in a countif function). However this is not working. Can anyone help me with finding wildcard or even a new formula to caluclate this accurately???

A snapshot of the data is:
[TABLE="width: 143"]
<TBODY>[TR]
[TD]</SPAN>
[/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD] [TABLE="width: 500"]
<TBODY>[TR]
[TD]Africa[/TD]
[TD]Mar (2-7)[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Jul [/TD]
[/TR]
[TR]
[TD]Africa[/TD]
[TD]Jun (7-9)[/TD]
[/TR]
[TR]
[TD]Middle East[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Feb (12-15)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]


Worth noting that it needs to be calculating continuously as new dates will always be added so pivots are out.

Thanks in advance

Laura

 

[Weazel]

maybe ...

=SUMPRODUCT((F2:F6="Africa")*ISNUMBER(SEARCH("*",G2:G6)))

 

[pgc01]

Hi Laura

Try:

=SUMPRODUCT(($F$3:$F$271="Africa")*($G$3:$G$271<>""))