I need help with a formula. If cells C12 through AG 12 have an "A" or an "F"
listed in any cell, I want to add and total the actual whole numbers listed
in cells C13 through AG13 (that have A or F above them in the column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI, totaling
all the numbers in C13 through AG13 that have "T" listed in any cell from C12
through AG 12. And so on. I have tried to figure this out from reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

=SUMIF(C12:AG12,"A",C13:AG13)+SUMIF(C12:AG12,"F",C 13:AG13)
=SUMIF(C12:AG12,"T",C13:AG13)
--
David Biddulph

"Rhonda Edwards" wrote in message
...
I need help with a formula. If cells C12 through AG 12 have an "A" or an
"F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI, totaling
all the numbers in C13 through AG13 that have "T" listed in any cell from
C12
through AG 12. And so on. I have tried to figure this out from reading
the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

sorry i am using this way to cammunicate my question bcz i dont know how i
can post my new question in this group. if any body can help kindly mail me
at
.

my question is as follows

Sheet A has
Col A: Inventory code
Col B: Description
Col C: Quantity

Sheet B has
Col A: Inventory code
Col B: Description
Col C: Quantity ( both sheets have same data but inventory code may be
different)

Sheet C required
Required: total quantity of sheet A and sheet B in Col. C of sheet C

Kindly help me to create a logical formula / vlook formula

Example

Sheet A

Col A Col B Col C
Inventory code Description Quantity
10001 pencil 25
10002 pen 50
10003 rubber 75

Sheet B
Col A Col B Col C
Inventory code Description Quantity
10001 pencil 25
10002 pen 50
10004 Duster 15


Sheet C
Col A Col B Col C
Inventory code Description Total Qty of sheet A & Sheet B
10001 pencil ? ( total required with the help of vlook formula)
10002 pen ?
10003 rubber ?
10004 Duster ?

I've given you an answer to your duplicate posting in another group.

Pete

vlook fomula wrote:

sorry i am using this way to cammunicate my question bcz i dont know how i
can post my new question in this group. if any body can help kindly mail me
at
.

my question is as follows

Sheet A has
Col A: Inventory code
Col B: Description
Col C: Quantity

Sheet B has
Col A: Inventory code
Col B: Description
Col C: Quantity ( both sheets have same data but inventory code may be
different)

Sheet C required
Required: total quantity of sheet A and sheet B in Col. C of sheet C

Kindly help me to create a logical formula / vlook formula

Example

Sheet A

Col A Col B Col C
Inventory code Description Quantity
10001 pencil 25
10002 pen 50
10003 rubber 75

Sheet B
Col A Col B Col C
Inventory code Description Quantity
10001 pencil 25
10002 pen 50
10004 Duster 15


Sheet C
Col A Col B Col C
Inventory code Description Total Qty of sheet A & Sheet B
10001 pencil ? ( total required with the help of vlook formula)
10002 pen ?
10003 rubber ?
10004 Duster ?

One way

For your first question
use
=SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13))

Happy new year
From New Zealand

"Rhonda Edwards" wrote in message
...
I need help with a formula. If cells C12 through AG 12 have an "A" or an
"F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI, totaling
all the numbers in C13 through AG13 that have "T" listed in any cell from
C12
through AG 12. And so on. I have tried to figure this out from reading
the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

Or even something in this form
=SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13))
Happy New Year from Nova Scotia
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Kuunders" wrote in message
...
One way

For your first question
use
=SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13))

Happy new year
From New Zealand

"Rhonda Edwards" wrote in
message ...
I need help with a formula. If cells C12 through AG 12 have an "A" or an
"F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell from
C12
through AG 12. And so on. I have tried to figure this out from reading
the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

Thanks Bernard
I tried to shorten it, but was using the old fashioned * format. Still
learning over here.........

Bill

"Bernard Liengme" wrote in message
...
Or even something in this form
=SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13))
Happy New Year from Nova Scotia
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Kuunders" wrote in message
...
One way

For your first question
use
=SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13))

Happy new year
From New Zealand

"Rhonda Edwards" wrote in
message ...
I need help with a formula. If cells C12 through AG 12 have an "A" or an
"F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell
from C12
through AG 12. And so on. I have tried to figure this out from reading
the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

Hello Bill
In the expression (range1)*(range2) the * operator can be thought of as AND
while (range1)+(range2) the + operator is OR

Think about {1, 0, 1}*{1, 1, 0}; we get {1, 0, 0} when each element in the
first array is multiplied by the correspond element in the second array. We
get 1 in the position were BOTH arrays have a 1.
While {1, 0, 1}+{1, 1, 0} yields {1, 1, 1} when each element in the first
array is added (binary adding giving either 0 or 1) to the correspond
element in the second array. We get a 1 in any position were EITHER array
has a 1.

My last 'lecture' for 2006!!!

hope this helps
all the best
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Kuunders" wrote in message
...
Thanks Bernard
I tried to shorten it, but was using the old fashioned * format. Still
learning over here.........

Bill

"Bernard Liengme" wrote in message
...
Or even something in this form
=SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13))
Happy New Year from Nova Scotia
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Kuunders" wrote in message
...
One way

For your first question
use
=SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13))

Happy new year
From New Zealand

"Rhonda Edwards" wrote in
message ...
I need help with a formula. If cells C12 through AG 12 have an "A" or
an "F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell
from C12
through AG 12. And so on. I have tried to figure this out from
reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

Bernard,

While {1, 0, 1}+{1, 1, 0} yields {1, 1, 1} when each element in the first
array is added (binary adding giving either 0 or 1) to the correspond
element in the second array. We get a 1 in any position were EITHER array
has a 1.

??

Whilst I appreciate that in the data for your formula it is not possible to
have any cell in Row 12 equalling both "A" and "B at the same time surely if
it is possible, like with A, B, A in C12:E12 and B, B, A in C13:E13, then
with the formula:

=SUMPRODUCT((C12:E12="A")*1+(C13:E13="B")*1)

(the *1's are of course to force 1's and 0's )

I get {1,0,1} + {1,1,0} like in your example but the resolves to:

{2,1,1} not {1, 1, 1}

Thus I get 4 not 3!

I know that you are right in what you say about binary arithmetic but surely
in this case we have only simple arithmetic?

Or am I missing something
--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Bernard Liengme" wrote in message
...
Hello Bill
In the expression (range1)*(range2) the * operator can be thought of as
AND
while (range1)+(range2) the + operator is OR

Think about {1, 0, 1}*{1, 1, 0}; we get {1, 0, 0} when each element in the
first array is multiplied by the correspond element in the second array.
We get 1 in the position were BOTH arrays have a 1.
While {1, 0, 1}+{1, 1, 0} yields {1, 1, 1} when each element in the first
array is added (binary adding giving either 0 or 1) to the correspond
element in the second array. We get a 1 in any position were EITHER array
has a 1.

My last 'lecture' for 2006!!!

hope this helps
all the best
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Kuunders" wrote in message
...
Thanks Bernard
I tried to shorten it, but was using the old fashioned * format. Still
learning over here.........

Bill

"Bernard Liengme" wrote in message
...
Or even something in this form
=SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13))
Happy New Year from Nova Scotia
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Kuunders" wrote in message
...
One way

For your first question
use
=SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13))

Happy new year
From New Zealand

"Rhonda Edwards" wrote in
message ...
I need help with a formula. If cells C12 through AG 12 have an "A" or
an "F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell
from C12
through AG 12. And so on. I have tried to figure this out from
reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

Thanks Bernard
I feel honoured ........
This is why I follow these groups,
You take the extra effort to explain different issues.
Bill

"Bernard Liengme" wrote in message
...
Hello Bill
In the expression (range1)*(range2) the * operator can be thought of as
AND
while (range1)+(range2) the + operator is OR

Think about {1, 0, 1}*{1, 1, 0}; we get {1, 0, 0} when each element in the
first array is multiplied by the correspond element in the second array.
We get 1 in the position were BOTH arrays have a 1.
While {1, 0, 1}+{1, 1, 0} yields {1, 1, 1} when each element in the first
array is added (binary adding giving either 0 or 1) to the correspond
element in the second array. We get a 1 in any position were EITHER array
has a 1.

My last 'lecture' for 2006!!!

hope this helps
all the best
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Kuunders" wrote in message
...
Thanks Bernard
I tried to shorten it, but was using the old fashioned * format. Still
learning over here.........

Bill

"Bernard Liengme" wrote in message
...
Or even something in this form
=SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13))
Happy New Year from Nova Scotia
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bill Kuunders" wrote in message
...
One way

For your first question
use
=SUMPRODUCT((C12:AG12="A")*(C13:AG13))+SUMPRODUCT( (C12:AG12="F")*(C13:AG13))

Happy new year
From New Zealand

"Rhonda Edwards" wrote in
message ...
I need help with a formula. If cells C12 through AG 12 have an "A" or
an "F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell
from C12
through AG 12. And so on. I have tried to figure this out from
reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

Hi Rhonda,

You have a simple formula for one condition already. For the situation of A
or F you can use:

=SUMPRODUCT(((C12:AG12="A")+(C12:AG12="F"))*C13:AG 13)

Or the more sophisticated:

=SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13))


--
Cheers,
Shane Devenshire


"Rhonda Edwards" wrote:

I need help with a formula. If cells C12 through AG 12 have an "A" or an "F"
listed in any cell, I want to add and total the actual whole numbers listed
in cells C13 through AG13 (that have A or F above them in the column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI, totaling
all the numbers in C13 through AG13 that have "T" listed in any cell from C12
through AG 12. And so on. I have tried to figure this out from reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

=SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13))

Another one:

=SUMPRODUCT(--(ISNUMBER(MATCH(C12:AG12,{"A","F"},0))),C13:AG13)

Biff

"ShaneDevenshire" wrote in
message ...
Hi Rhonda,

You have a simple formula for one condition already. For the situation of
A
or F you can use:

=SUMPRODUCT(((C12:AG12="A")+(C12:AG12="F"))*C13:AG 13)

Or the more sophisticated:

=SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13))


--
Cheers,
Shane Devenshire


"Rhonda Edwards" wrote:

I need help with a formula. If cells C12 through AG 12 have an "A" or an
"F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell from
C12
through AG 12. And so on. I have tried to figure this out from reading
the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

Try this:

SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)


"Rhonda Edwards" wrote:

I need help with a formula. If cells C12 through AG 12 have an "A" or an "F"
listed in any cell, I want to add and total the actual whole numbers listed
in cells C13 through AG13 (that have A or F above them in the column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI, totaling
all the numbers in C13 through AG13 that have "T" listed in any cell from C12
through AG 12. And so on. I have tried to figure this out from reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

Well I see I started something with the {"F","A"}.

Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and
=SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical.

However, I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)
to work and I'm not sure I follow the logic?

--
Thanks,
Shane Devenshire


"Teethless mama" wrote:

Try this:

SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)


"Rhonda Edwards" wrote:

I need help with a formula. If cells C12 through AG 12 have an "A" or an "F"
listed in any cell, I want to add and total the actual whole numbers listed
in cells C13 through AG13 (that have A or F above them in the column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI, totaling
all the numbers in C13 through AG13 that have "T" listed in any cell from C12
through AG 12. And so on. I have tried to figure this out from reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)

Don't worry about it. It won't work. It's not syntatically correct for one
thing and even if you correct that, it still won't work.

Biff

"ShaneDevenshire" wrote in
message ...
Well I see I started something with the {"F","A"}.

Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and
=SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical.

However, I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)
to work and I'm not sure I follow the logic?

--
Thanks,
Shane Devenshire


"Teethless mama" wrote:

Try this:

SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)


"Rhonda Edwards" wrote:

I need help with a formula. If cells C12 through AG 12 have an "A" or
an "F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell
from C12
through AG 12. And so on. I have tried to figure this out from
reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

Fellas ... You're all forgetting that you're going *across*, not down!

This works fine for me:

=SUMPRODUCT((C12:AG12={"A";"F"})*C13:AG13)


Hit <F9 after selecting "C12:AG12={"A","F"}",
And you see only 2 returns, with the rest being #N/A,
meaning that the formula is polling down.

The semi-colon just tells it to poll across (I THINK)!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)

Don't worry about it. It won't work. It's not syntatically correct for one
thing and even if you correct that, it still won't work.

Biff

"ShaneDevenshire" wrote in
message ...
Well I see I started something with the {"F","A"}.

Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and
=SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical.

However, I can't get the idea
SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)
to work and I'm not sure I follow the logic?

--
Thanks,
Shane Devenshire


"Teethless mama" wrote:

Try this:

SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)


"Rhonda Edwards" wrote:

I need help with a formula. If cells C12 through AG 12 have an "A"
or
an "F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell
from C12
through AG 12. And so on. I have tried to figure this out from
reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

I didn't even think of that!

Biff

"Ragdyer" wrote in message
...
Fellas ... You're all forgetting that you're going *across*, not down!

This works fine for me:

=SUMPRODUCT((C12:AG12={"A";"F"})*C13:AG13)


Hit <F9 after selecting "C12:AG12={"A","F"}",
And you see only 2 returns, with the rest being #N/A,
meaning that the formula is polling down.

The semi-colon just tells it to poll across (I THINK)!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)

Don't worry about it. It won't work. It's not syntatically correct for
one
thing and even if you correct that, it still won't work.

Biff

"ShaneDevenshire" wrote in
message ...
Well I see I started something with the {"F","A"}.

Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and
=SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical.

However, I can't get the idea
SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)
to work and I'm not sure I follow the logic?

--
Thanks,
Shane Devenshire


"Teethless mama" wrote:

Try this:

SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)


"Rhonda Edwards" wrote:

I need help with a formula. If cells C12 through AG 12 have an "A"
or
an "F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell
from C12
through AG 12. And so on. I have tried to figure this out from
reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda

So that's what I was doing wrong!! I was thinking that I've seen a way to do
this w/sumproduct and an array constant, but couldn't get it.

Thanks for posting, Ragdyer, that was bugging me.


"Ragdyer" wrote:

Fellas ... You're all forgetting that you're going *across*, not down!

This works fine for me:

=SUMPRODUCT((C12:AG12={"A";"F"})*C13:AG13)


Hit <F9 after selecting "C12:AG12={"A","F"}",
And you see only 2 returns, with the rest being #N/A,
meaning that the formula is polling down.

The semi-colon just tells it to poll across (I THINK)!<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
I can't get the idea SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)

Don't worry about it. It won't work. It's not syntatically correct for one
thing and even if you correct that, it still won't work.

Biff

"ShaneDevenshire" wrote in
message ...
Well I see I started something with the {"F","A"}.

Of course =SUM(SUMIF(C12:AG12,{"A","F"},C13:AG13)) and
=SUMPRODUCT(SUMIF(C12:AG12,{"A","F"},C13:AG13)) are identical.

However, I can't get the idea
SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)
to work and I'm not sure I follow the logic?

--
Thanks,
Shane Devenshire


"Teethless mama" wrote:

Try this:

SUMPRODUCT(--(C12:AG12,{"A","F"})*C13:AG13)


"Rhonda Edwards" wrote:

I need help with a formula. If cells C12 through AG 12 have an "A"
or
an "F"
listed in any cell, I want to add and total the actual whole numbers
listed
in cells C13 through AG13 (that have A or F above them in the
column)totaling
them in cell in AH (where I merged the cells 11-13). Then in AI,
totaling
all the numbers in C13 through AG13 that have "T" listed in any cell
from C12
through AG 12. And so on. I have tried to figure this out from
reading the
help menu in Excel but I haven't been able to; can someone help.
Thanks,
--
Rhonda

--
Rhonda