Excel formula help...please

[Guest]

H

1. worksheet

Colomn H Column J Column M Column N Column
(State Name) Code(4,5,7) Value Value Valu

-AZ..........................4 .............$
-AZ..........................4 .............$
-CA..........................5....................................1
-CA..........................5....................................1
-MA..........................7...............................................................3
-MA..........................7...............................................................3

2. Worksheet B - would like to accomplis

Summary total

-AZ = $1
-CA = $2
-MA = $3

I'd like to have a formula in a cell, which mean to look up in worksheet B, by STATE, CODE # and total up

THANKS FOR THE HEL

Jo

 

[Frank Kabel]

Hi

1. worksheet B

Colomn H Column J Column M Column N
Column P (State Name) Code(4,5,7) Value Value
Value

-AZ..........................4 .............$5
-AZ..........................4 .............$5
-CA..........................5....................................10
-CA..........................5....................................10
-MA..........................7....................................... .........................30
.........................30

2. Worksheet B - would like to accomplish

Summary total:

-AZ = $10
-CA = $20
-MA = $30

I'd like to have a formula in a cell, which mean to look up in
worksheet B, by STATE, CODE # and total up.

THANKS FOR THE HELP

Jon

Hi Jon
try
=SUMPRODUCT(('worksheetB'!H1:H1000="AZ")*('worksheetB'!P11000="Staten
ame"),'worksheetB'!M1:M1000)
i'm not so sure about the column references as my newsreader added some
line breaks. So the above formula compares the columns H and P with
your lookup criteria and summs the values in column M

Frank

 

[Roger Govier]

Hi Jon

You seem to use Worksheet B twice. Assuming your data in on Worksheet B, and
you want your summary on Worksheet A, put the states in Column A starting at
A2 and enter in cell B2
=SUMPRODUCT(--('Worksheet B'!$H$2:$H$1000=A2)*('Worksheet B'!$M$2:$P$1000))
Copy down columnB for as many entries as you have states.

--
Regards
Roger Govier

Hi

1. worksheet B

Colomn H Column J Column M Column N Column P
(State Name) Code(4,5,7) Value Value Value

-AZ..........................4 .............$5
-AZ..........................4 .............$5
-CA..........................5....................................10
-CA..........................5....................................10
-MA..........................7............................................ ....................30
....................30

2. Worksheet B - would like to accomplish

Summary total:

-AZ = $10
-CA = $20
-MA = $30

I'd like to have a formula in a cell, which mean to look up in worksheet

B, by STATE, CODE # and total up.

 

[Guest]

hello Roger

Perhaps i'm confused you. here's my scenario: Find code"4", in column J and H, of each state and sum it up.
hope i'm not confuse again

Thanks
Jo


----- Roger Govier wrote: ----

Hi Jo

You seem to use Worksheet B twice. Assuming your data in on Worksheet B, an
you want your summary on Worksheet A, put the states in Column A starting a
A2 and enter in cell B
=SUMPRODUCT(--('Worksheet B'!$H$2:$H$1000=A2)*('Worksheet B'!$M$2:$P$1000)
Copy down columnB for as many entries as you have states

 

[Guest]

Frank

Perhaps i'm confused you. here's my scenario: Find code"4", in column J and H, of each state and sum it up.
hope i'm not confuse again

Thanks
Jo



----- Frank Kabel wrote: ----

Jon wrote

H
Column P (State Name) Code(4,5,7) Value Valu
Valu
-AZ..........................4 .............$
-CA..........................5....................................1
-CA..........................5....................................1
-MA..........................7...................................... .........................3
-CA = $2
-MA = $3
worksheet B, by STATE, CODE # and total up

Hi Jo
tr
=SUMPRODUCT(('worksheetB'!H1:H1000="AZ")*('worksheetB'!P11000="State
ame"),'worksheetB'!M1:M1000
i'm not so sure about the column references as my newsreader added som
line breaks. So the above formula compares the columns H and P wit
your lookup criteria and summs the values in column

Fran

 

[Roger Govier]

Hi Jon

From your example, whether you sum by State Name (column H) or State Number
(Column J) you would get the same answer.

If you want to use Sate Number, then put State Number in Sheet B, A2:A100
and use
=SUMPRODUCT(--('Worksheet B'!$J$2:$J$1000=A2)*('Worksheet
B'!$M$2:$P$1000))


--
Regards
Roger Govier

hello Roger,

Perhaps i'm confused you. here's my scenario: Find code"4", in column J

and H, of each state and sum it up.

 

[Frank Kabel]

Hi Jon
if your state name (for lookin up in column H) is stored in B2 try
=SUMPRODUCT(('worksheetB'!H1:H1000=B2)*('worksheetB'!J1:j1000=4),'works
heetB'!M1:M1000)
will sum up the values in column M

Frank