i managed to fix the error for below

thanks for your help!!

"judy" wrote:

Sorry for not being concise in my earlier email

For example, i have Local SG, Local CN ... and another category that reads
Sub 1, Sub 2, Sub 3

The earlier ISNUMBER has helped me to calculate the Local SG and all the Sub
categories numbers.

However, the ISERROR formula is calculating all the fields by just excluding
Local SG. I'm trying to get the formula where it excludes Local SG & all the
Sub categories by using below

=(SUMPRODUCT(--(ISERROR(SEARCH("Local
SG",$F$24:$F$43))),--($G$24:$G$43="Plan"),$H$24:$H$43))-(SUMPRODUCT(--(ISERROR(SEARCH("Sub*",$F$24:$F$43))),--($G$24:$G$43="Plan"),$H$24:$H$43))

But the number is returning a negative value where it should be positive.
Any idea?

thanks a million for ur help!
--
judy goh


"T. Valko" wrote:

For "Local SG" :

=SUMPRODUCT(--(ISNUMBER(SEARCH("Local
SG",$E$24:$E$43))),--($G$24:$G$43="Actual"),$H$24:$H$43)

For all others:

=SUMPRODUCT(--(ISERROR(SEARCH("Local
SG",$E$24:$E$43))),--($G$24:$G$43="Actual"),$H$24:$H$43)



--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Hi Biff

Is there a way this formula can further cater to below?

For example
I have local Sg, local CN, local CZ, local MY fields and i'm trying to
achieve below:

SUM all for local SG compared to SUM all for the rest of the regions,
excluding SG.

=SUMPRODUCT(--(ISNUMBER(SEARCH("Local",$E$24:$E$43))),--($G$24:$G$43="Actual"),$H$24:$H$43)
--
judy goh


"T. Valko" wrote:

Is the wildcard feature not available in SUM+IF

No, it can't be used directly in those functions.

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Local",$E$24:$E$43))),--($G$24:$G$43="Actual"),$H$24:$H$43)


--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Hello!
I was able to use wildcards in SUMIF formulas but it just don't work in
SUM+IF statement. Is the wildcard feature not available in SUM+IF
statement
or there's something wrong with my formula?

=SUMIF($E$2:$E$300, "Local*", $F$2:$F$300)
This will help me sum up the F column if column E contains Local 1,
Local
2,
Local 3 etc

The formula I'm trying to create now is as follow
=SUM(IF(($E$24:$E$43="Local*")*($G$24:$G$43="Actua l"),$H$24:$H$43))
However, the formula is not looking at Local 1, Local 2, Local 3 of
column
E. It doesnt seem to recognise this feature.

Can any of you help advise me on above? Thanks soo much!!!