Hi I am trying to create a formula but having trouble the answer i want is

=sum of coulmn C if column A = "" and if cloumn B begins with ""

Please help kind hearted souls!!!

nich wrote:
Hi I am trying to create a formula but having trouble the answer i want is

=sum of coulmn C if column A = "" and if cloumn B begins with ""

Please help kind hearted souls!!!

Here is an example:

A B C
1 m bays 564
2 n bat 264
3 m bayo 223

sum C if a=m and b begins with bay

i.e in this case c1 plus c 3

On Wed, 22 Jul 2009 10:05:41 GMT, "nich" <u53467@uwe wrote:

nich wrote:
Hi I am trying to create a formula but having trouble the answer i want is

=sum of coulmn C if column A = "" and if cloumn B begins with ""

Please help kind hearted souls!!!

Here is an example:

A B C
1 m bays 564
2 n bat 264
3 m bayo 223

sum C if a=m and b begins with bay

i.e in this case c1 plus c 3


Try this formula:

=SUMPRODUCT((A1:A100="m")*(LEFT(B1:B100,3)="bay")* (C1:C100))

Change the 100 in three places to reflect the number of data rows you
that have.

Hope this helps / Lars-Åke

=SUMPRODUCT(--(A1:A3="m"),--(LEFT(B1:B3,3)="bay"),C1:C3)
Regards,
Stefi

€žnich€ ezt Ã*rta:

nich wrote:
Hi I am trying to create a formula but having trouble the answer i want is

=sum of coulmn C if column A = "" and if cloumn B begins with ""

Please help kind hearted souls!!!

Here is an example:

A B C
1 m bays 564
2 n bat 264
3 m bayo 223

sum C if a=m and b begins with bay

i.e in this case c1 plus c 3

Lars Thank you for getting back!

I'm still having trouble with that one not sure why though :-(

I have had to change the references as they're on a different tabI'll how you
formula:

=SUMPRODUCT(('SAP Update'!H1:H1000="SC.1343.001")*(LEFT('SAP Update'!M1:M1000,
3)="P03")*('SAP Update'!C1:C1000))

Can you tell where I've gone wrong?

Many Thanks

Nich

Lars-Ã…ke Aspelin wrote:
Hi I am trying to create a formula but having trouble the answer i want is

[quoted text clipped - 12 lines]

i.e in this case c1 plus c 3

Try this formula:

=SUMPRODUCT((A1:A100="m")*(LEFT(B1:B100,3)="bay") *(C1:C100))

Change the 100 in three places to reflect the number of data rows you
that have.

Hope this helps / Lars-Ã…ke

Stefi you are a genius!!!!!

Thank you SOOOOOOOOOOOOOO much!!!!!

Stefi wrote:
=SUMPRODUCT(--(A1:A3="m"),--(LEFT(B1:B3,3)="bay"),C1:C3)
Regards,
Stefi

€žnich€ ezt Ã*rta:

Hi I am trying to create a formula but having trouble the answer i want is

[quoted text clipped - 12 lines]

i.e in this case c1 plus c 3

I see nothing wrong in the formula.
What is the trouble? What result do you get? What do you expect?

Lars-Åke

On Wed, 22 Jul 2009 10:48:02 GMT, "nich" <u53467@uwe wrote:

Lars Thank you for getting back!

I'm still having trouble with that one not sure why though :-(

I have had to change the references as they're on a different tabI'll how you
formula:

=SUMPRODUCT(('SAP Update'!H1:H1000="SC.1343.001")*(LEFT('SAP Update'!M1:M1000,
3)="P03")*('SAP Update'!C1:C1000))

Can you tell where I've gone wrong?

Many Thanks

Nich

Lars-Åke Aspelin wrote:
Hi I am trying to create a formula but having trouble the answer i want is

[quoted text clipped - 12 lines]

i.e in this case c1 plus c 3

Try this formula:

=SUMPRODUCT((A1:A100="m")*(LEFT(B1:B100,3)="bay" )*(C1:C100))

Change the 100 in three places to reflect the number of data rows you
that have.

Hope this helps / Lars-Åke