Excel formula help...please

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Jon said:
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
Click to expand...

Hi Jon
try
=SUMPRODUCT(('worksheetB'!H1:H1000="AZ")*('worksheetB'!P1:P1000="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
 
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
Jon said:
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
Click to expand...
B, by STATE, CODE # and total up.
 
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
 
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
Click to expand...

Hi Jo
tr
=SUMPRODUCT(('worksheetB'!H1:H1000="AZ")*('worksheetB'!P1:P1000="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
 
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
Jon said:
hello Roger,

Perhaps i'm confused you. here's my scenario: Find code"4", in column J
Click to expand...
and H, of each state and sum it up.
 
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
 
Back
Top