need help with a formula

[Rhonda Edwards]

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,

 

[David Biddulph]

=SUMIF(C12:AG12,"A",C13:AG13)+SUMIF(C12:AG12,"F",C13:AG13)
=SUMIF(C12:AG12,"T",C13:AG13)

 

[Bill Kuunders]

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

 

[Bernard Liengme]

Or even something in this form
=SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13))
Happy New Year from Nova Scotia

 

[ShaneDevenshire]

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:AG13)

Or the more sophisticated:

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

 

[Bill Kuunders]

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

Bill

 

[T. Valko]

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

Another one:

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

Biff

 

[Bernard Liengme]

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

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

Bill

Or even something in this form
=SUMPRODUCT((C12:AG12="A")+(C12:AG12="B"), (C13:AG13))
Happy New Year from Nova Scotia

 

[Teethless mama]

Try this:

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

 

[ShaneDevenshire]

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?

 

[T. Valko]

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

 

[Ragdyer]

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>

 

[T. Valko]

I didn't even think of that!

Biff

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>

 

[Ragdyer]

That's rare for you!<bg>

HAPPY NEW YEAR Biff
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I didn't even think of that!

Biff

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

!

--------------------------------------------------------------------------

-

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

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


:

Try this:

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


:

I need help with a formula. If cells C12 through AG 12 have an

"A"
or

 

[JMB]

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.

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

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

 

[Bill Kuunders]

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

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

 

[vlook fomula]

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
(e-mail address removed).

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 ?

 

[Pete_UK]

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

Pete