I have a large worksheet that has a product name and then in the next cell a
sum, how do I sum only those equal to a specific product name. I have a list
of the products at the bottom of my spreadsheet where I want to put this sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not sure I
am even giving the right terms). Right now, I have to Ctrl click every cell
that is for a specific product. I wind up having to sum several cells and
then a sum of those groupings because of excel's limit on the number of cells
that can be included in a formula.

=SUMIF(A:A,A1,B:B)
A1= Batch


"Jana" wrote:

I have a large worksheet that has a product name and then in the next cell a
sum, how do I sum only those equal to a specific product name. I have a list
of the products at the bottom of my spreadsheet where I want to put this sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not sure I
am even giving the right terms). Right now, I have to Ctrl click every cell
that is for a specific product. I wind up having to sum several cells and
then a sum of those groupings because of excel's limit on the number of cells
that can be included in a formula.

Oh my Gosh!!!! Thanks So much, I think this is going to work. I did it on a
test worksheet and appears to work perfectly. Now going to apply to the
monster spreadsheet. HUGE Thanks!

"Jana" wrote:

I have a large worksheet that has a product name and then in the next cell a
sum, how do I sum only those equal to a specific product name. I have a list
of the products at the bottom of my spreadsheet where I want to put this sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not sure I
am even giving the right terms). Right now, I have to Ctrl click every cell
that is for a specific product. I wind up having to sum several cells and
then a sum of those groupings because of excel's limit on the number of cells
that can be included in a formula.

It's not working in my large spread sheet.
Product Name is in Column F
Product Total is in Column G
The Summary Table I also have the product in Column F with the Total Sum of
that product then in G Should the formula read as follows:
=SUMIF(F:F,F327,G:G)
G327=Batch
??
"Jana" wrote:

I have a large worksheet that has a product name and then in the next cell a
sum, how do I sum only those equal to a specific product name. I have a list
of the products at the bottom of my spreadsheet where I want to put this sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not sure I
am even giving the right terms). Right now, I have to Ctrl click every cell
that is for a specific product. I wind up having to sum several cells and
then a sum of those groupings because of excel's limit on the number of cells
that can be included in a formula.

Here is my actual forumula: =SUMIF(F1:F334,"Batch",G1:G334), but it's
returning a number, just not the right one, do I have to do something special
to account for the blank lines?

"Jana" wrote:

I have a large worksheet that has a product name and then in the next cell a
sum, how do I sum only those equal to a specific product name. I have a list
of the products at the bottom of my spreadsheet where I want to put this sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not sure I
am even giving the right terms). Right now, I have to Ctrl click every cell
that is for a specific product. I wind up having to sum several cells and
then a sum of those groupings because of excel's limit on the number of cells
that can be included in a formula.

That should be okay.

Start by reducing the range, and checking, to say

=SUMIF(F1:F167,"Batch"mG1:G167)

If okay, increase by a half, if not, decrease by a half. When it goes wrong
check that range of data.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jana" wrote in message
...
Here is my actual forumula: =SUMIF(F1:F334,"Batch",G1:G334), but it's
returning a number, just not the right one, do I have to do something
special
to account for the blank lines?

"Jana" wrote:

I have a large worksheet that has a product name and then in the next
cell a
sum, how do I sum only those equal to a specific product name. I have a
list
of the products at the bottom of my spreadsheet where I want to put this
sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not
sure I
am even giving the right terms). Right now, I have to Ctrl click every
cell
that is for a specific product. I wind up having to sum several cells
and
then a sum of those groupings because of excel's limit on the number of
cells
that can be included in a formula.

Did that on a seperate spreadsheet and even took out blank lines, only had 10
rows, didn't bring back the right value...ugh

"Bob Phillips" wrote:

That should be okay.

Start by reducing the range, and checking, to say

=SUMIF(F1:F167,"Batch"mG1:G167)

If okay, increase by a half, if not, decrease by a half. When it goes wrong
check that range of data.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jana" wrote in message
...
Here is my actual forumula: =SUMIF(F1:F334,"Batch",G1:G334), but it's
returning a number, just not the right one, do I have to do something
special
to account for the blank lines?

"Jana" wrote:

I have a large worksheet that has a product name and then in the next
cell a
sum, how do I sum only those equal to a specific product name. I have a
list
of the products at the bottom of my spreadsheet where I want to put this
sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not
sure I
am even giving the right terms). Right now, I have to Ctrl click every
cell
that is for a specific product. I wind up having to sum several cells
and
then a sum of those groupings because of excel's limit on the number of
cells
that can be included in a formula.

List out the 10 rows then and tell us what you get and what you expect.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jana" wrote in message
...
Did that on a seperate spreadsheet and even took out blank lines, only had
10
rows, didn't bring back the right value...ugh

"Bob Phillips" wrote:

That should be okay.

Start by reducing the range, and checking, to say

=SUMIF(F1:F167,"Batch"mG1:G167)

If okay, increase by a half, if not, decrease by a half. When it goes
wrong
check that range of data.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jana" wrote in message
...
Here is my actual forumula: =SUMIF(F1:F334,"Batch",G1:G334), but it's
returning a number, just not the right one, do I have to do something
special
to account for the blank lines?

"Jana" wrote:

I have a large worksheet that has a product name and then in the
next
cell a
sum, how do I sum only those equal to a specific product name. I
have a
list
of the products at the bottom of my spreadsheet where I want to put
this
sum.
The worksheet is an export of data from a datase which brings over
blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says
if
A1="Batch" then Sum all hours (keep in mind, major novice here and
not
sure I
am even giving the right terms). Right now, I have to Ctrl click
every
cell
that is for a specific product. I wind up having to sum several
cells
and
then a sum of those groupings because of excel's limit on the number
of
cells
that can be included in a formula.

Jana,

Some of your entries may include leading or trailing spaces - try this
variation to see if the value increases:

=SUMIF(F1:F334,"Batch*",G1:G334)

Sumif can accept wildcards, which is what the asterisk is for, so if
this improves the situation you might like to make it "*Batch*",
although you need to be wary of the other values that could appear in
column F.

Hope this helps.

Pete

Jana wrote:
Did that on a seperate spreadsheet and even took out blank lines, only had 10
rows, didn't bring back the right value...ugh

"Bob Phillips" wrote:

That should be okay.

Start by reducing the range, and checking, to say

=SUMIF(F1:F167,"Batch"mG1:G167)

If okay, increase by a half, if not, decrease by a half. When it goes wrong
check that range of data.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jana" wrote in message
...
Here is my actual forumula: =SUMIF(F1:F334,"Batch",G1:G334), but it's
returning a number, just not the right one, do I have to do something
special
to account for the blank lines?

"Jana" wrote:

I have a large worksheet that has a product name and then in the next
cell a
sum, how do I sum only those equal to a specific product name. I have a
list
of the products at the bottom of my spreadsheet where I want to put this
sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not
sure I
am even giving the right terms). Right now, I have to Ctrl click every
cell
that is for a specific product. I wind up having to sum several cells
and
then a sum of those groupings because of excel's limit on the number of
cells
that can be included in a formula.

It worked on a smaller scale of the worksheet with 50 rows, but not on the
master that has over 288 rows. Is it because of excel's limits on how many
cells can be included in a calc?

"Pete_UK" wrote:

Jana,

Some of your entries may include leading or trailing spaces - try this
variation to see if the value increases:

=SUMIF(F1:F334,"Batch*",G1:G334)

Sumif can accept wildcards, which is what the asterisk is for, so if
this improves the situation you might like to make it "*Batch*",
although you need to be wary of the other values that could appear in
column F.

Hope this helps.

Pete

Jana wrote:
Did that on a seperate spreadsheet and even took out blank lines, only had 10
rows, didn't bring back the right value...ugh

"Bob Phillips" wrote:

That should be okay.

Start by reducing the range, and checking, to say

=SUMIF(F1:F167,"Batch"mG1:G167)

If okay, increase by a half, if not, decrease by a half. When it goes wrong
check that range of data.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jana" wrote in message
...
Here is my actual forumula: =SUMIF(F1:F334,"Batch",G1:G334), but it's
returning a number, just not the right one, do I have to do something
special
to account for the blank lines?

"Jana" wrote:

I have a large worksheet that has a product name and then in the next
cell a
sum, how do I sum only those equal to a specific product name. I have a
list
of the products at the bottom of my spreadsheet where I want to put this
sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not
sure I
am even giving the right terms). Right now, I have to Ctrl click every
cell
that is for a specific product. I wind up having to sum several cells
and
then a sum of those groupings because of excel's limit on the number of
cells
that can be included in a formula.

That did the trick. I wasn't able to copy the formula down and then go in and
change the "names*", but if I cleared all and then typed in each one
individually it worked, very strange, but it is working and I am so gratefull
for this discussion group. I will be back! Thanks again for everyone's
help!!!!

"Pete_UK" wrote:

Jana,

Some of your entries may include leading or trailing spaces - try this
variation to see if the value increases:

=SUMIF(F1:F334,"Batch*",G1:G334)

Sumif can accept wildcards, which is what the asterisk is for, so if
this improves the situation you might like to make it "*Batch*",
although you need to be wary of the other values that could appear in
column F.

Hope this helps.

Pete

Jana wrote:
Did that on a seperate spreadsheet and even took out blank lines, only had 10
rows, didn't bring back the right value...ugh

"Bob Phillips" wrote:

That should be okay.

Start by reducing the range, and checking, to say

=SUMIF(F1:F167,"Batch"mG1:G167)

If okay, increase by a half, if not, decrease by a half. When it goes wrong
check that range of data.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jana" wrote in message
...
Here is my actual forumula: =SUMIF(F1:F334,"Batch",G1:G334), but it's
returning a number, just not the right one, do I have to do something
special
to account for the blank lines?

"Jana" wrote:

I have a large worksheet that has a product name and then in the next
cell a
sum, how do I sum only those equal to a specific product name. I have a
list
of the products at the bottom of my spreadsheet where I want to put this
sum.
The worksheet is an export of data from a datase which brings over blank
lines and rows that don't have data in them. ex below:
D06D548D VOIP34 Support Rating and Processing of Data Usage
11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Batch 500 635
Total Estimate Percentage 127%Total Hours 500 635
D06D548I Expansion of Event Master, UDF and CTD Monetary
Fields 11/03/2007 A Due: CS:5/1/07 QS:8/7/07 CCS Online 446 566
UDF 75 95
Batch 594 754
CTUPDATE 112
Total Estimate Percentage 127%Total Hours
1,227 1,558
I would like to have some kind of an IF statement I think that says if
A1="Batch" then Sum all hours (keep in mind, major novice here and not
sure I
am even giving the right terms). Right now, I have to Ctrl click every
cell
that is for a specific product. I wind up having to sum several cells
and
then a sum of those groupings because of excel's limit on the number of
cells
that can be included in a formula.

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!!!

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!!!

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!!!

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!!!

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!!!

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!!!

Hi Biff
I'm encountering another issue.
the formula is working for the Product A.
when i applied the formula to Product B, the values show as 0 (though the
details have a value tied to it)

I tried to re-calculate the formula by doing a Ctrl-ALT-F9 but it's still
showing as 0 values. Any idea why this is happening?

Product A, Product B
Local SG 25, 0
Local Others 7, 0
Sub 8,0
--
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!!!

Can you post a small sample of your data so I can what you're trying to do?

--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Hi Biff
I'm encountering another issue.
the formula is working for the Product A.
when i applied the formula to Product B, the values show as 0 (though the
details have a value tied to it)

I tried to re-calculate the formula by doing a Ctrl-ALT-F9 but it's still
showing as 0 values. Any idea why this is happening?

Product A, Product B
Local SG 25, 0
Local Others 7, 0
Sub 8,0
--
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!!!

Can you post a small sample of your data so I can what you're trying to do?

Geezzzz! I left out the most important word.

Can you post a small sample of your data so I can see what you're trying to
do?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Can you post a small sample of your data so I can what you're trying to
do?

--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Hi Biff
I'm encountering another issue.
the formula is working for the Product A.
when i applied the formula to Product B, the values show as 0 (though the
details have a value tied to it)

I tried to re-calculate the formula by doing a Ctrl-ALT-F9 but it's still
showing as 0 values. Any idea why this is happening?

Product A, Product B
Local SG 25, 0
Local Others 7, 0
Sub 8,0
--
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!!!

Biff, i re-do the format in a new worksheet n the formula is working

i need your advice on below
Is there a formula to calculate below?

For example, we are currently in the month of May,
Actual consultants utilized (past months) vs No.
of consultants utilized (Plan) -- forward month
Region A Jan - April data
May - Dec data
Region B Jan - April data
May - Dec data
Region C Jan - April data
May - Dec data
When we reach the month of June, the formula will be to detect the date and
make a auto-calculation?

Data Details
Region Resource Utilization Month No of Consultants Utilized (Plan) Plan
Days Plan Util (%) No of Consultants Utilized (Actual) Actual Days Util (%)
CN BPM Jan. 2008 18 22 0.438016529 0 0 0
Cn BPM Feb. 2008 13 17 0.444444444 13 18 0.424242424
Cn BPM Mar. 2008 22 30 0.735930736 21 28.5 0.602813853
Cn BPm Apr. 2008 20 16 0.361471861 13 18.5 0.364718615
CZ BPm May. 2008 17 27 0.620454545 0 0 0
CZ BPm June. 2008 18 32 0.807017544 0 0 0
PH BPm July. 2008 19 36 0.814645309 0 0 0
PH BPm Aug. 2008 18 2 0.807017544 0 0 0
PH BPm Sept. 2008 20 71 0.843181818 0 0 0
PH BPm Oct. 2008 20 9 0.8475 0 0 0
MY BPm Nov. 2008 20 37 0.8175 0 0 0
MY BPm Dec. 2008 20 35 0.815217391 0 0 0

thanks...
judy goh


"T. Valko" wrote:

Can you post a small sample of your data so I can what you're trying to do?

Geezzzz! I left out the most important word.

Can you post a small sample of your data so I can see what you're trying to
do?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Can you post a small sample of your data so I can what you're trying to
do?

--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Hi Biff
I'm encountering another issue.
the formula is working for the Product A.
when i applied the formula to Product B, the values show as 0 (though the
details have a value tied to it)

I tried to re-calculate the formula by doing a Ctrl-ALT-F9 but it's still
showing as 0 values. Any idea why this is happening?

Product A, Product B
Local SG 25, 0
Local Others 7, 0
Sub 8,0
--
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!!!

When we reach the month of June, the formula will be to
detect the date and make a auto-calculation?

A calculation of what?

I'm not following you on this.

--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Biff, i re-do the format in a new worksheet n the formula is working

i need your advice on below
Is there a formula to calculate below?

For example, we are currently in the month of May,
Actual consultants utilized (past months) vs
No.
of consultants utilized (Plan) -- forward month
Region A Jan - April data
May - Dec data
Region B Jan - April data
May - Dec data
Region C Jan - April data
May - Dec data
When we reach the month of June, the formula will be to detect the date
and
make a auto-calculation?

Data Details
Region Resource Utilization Month No of Consultants Utilized (Plan) Plan
Days Plan Util (%) No of Consultants Utilized (Actual) Actual Days Util
(%)
CN BPM Jan. 2008 18 22 0.438016529 0 0 0
Cn BPM Feb. 2008 13 17 0.444444444 13 18 0.424242424
Cn BPM Mar. 2008 22 30 0.735930736 21 28.5 0.602813853
Cn BPm Apr. 2008 20 16 0.361471861 13 18.5 0.364718615
CZ BPm May. 2008 17 27 0.620454545 0 0 0
CZ BPm June. 2008 18 32 0.807017544 0 0 0
PH BPm July. 2008 19 36 0.814645309 0 0 0
PH BPm Aug. 2008 18 2 0.807017544 0 0 0
PH BPm Sept. 2008 20 71 0.843181818 0 0 0
PH BPm Oct. 2008 20 9 0.8475 0 0 0
MY BPm Nov. 2008 20 37 0.8175 0 0 0
MY BPm Dec. 2008 20 35 0.815217391 0 0 0

thanks...
judy goh


"T. Valko" wrote:

Can you post a small sample of your data so I can what you're trying to
do?

Geezzzz! I left out the most important word.

Can you post a small sample of your data so I can see what you're trying
to
do?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Can you post a small sample of your data so I can what you're trying to
do?

--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Hi Biff
I'm encountering another issue.
the formula is working for the Product A.
when i applied the formula to Product B, the values show as 0 (though
the
details have a value tied to it)

I tried to re-calculate the formula by doing a Ctrl-ALT-F9 but it's
still
showing as 0 values. Any idea why this is happening?

Product A, Product B
Local SG 25, 0
Local Others 7, 0
Sub 8,0
--
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!!!