| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Insert values based on 2 criterias
A B C D
1 France Spain Italy 2 400010 6 % 1 % 3 % 3 400020 2 % 4 % 3 % 4 400040 1 % 2 % 3 % 5 . 6 . 7 . 8 I would like to put in the percentages into another sheet that looks like this: 400010 France 6% 400020 Spain 4% 400040 Italy 3% How can i do that. I've tried with if sentences but it does not work |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert values based on 2 criterias
Hi,
I'm making the assumption you have 400010 France on your 'other sheet and it's just the percentage you want. If that's correct try this:- =SUMPRODUCT((Sheet1!A2:A4=A1)*(Sheet1!B1:D1=B1)*(S heet1!B2:D4)) This returns the % with your table laid out like below on sheet 1. Mike "wamz" wrote: A B C D 1 France Spain Italy 2 400010 6 % 1 % 3 % 3 400020 2 % 4 % 3 % 4 400040 1 % 2 % 3 % 5 . 6 . 7 . 8 I would like to put in the percentages into another sheet that looks like this: 400010 France 6% 400020 Spain 4% 400040 Italy 3% How can i do that. I've tried with if sentences but it does not work |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert values based on 2 criterias
Thanks alot, that was exactly what i was looking for
"Mike H" wrote: Hi, I'm making the assumption you have 400010 France on your 'other sheet and it's just the percentage you want. If that's correct try this:- =SUMPRODUCT((Sheet1!A2:A4=A1)*(Sheet1!B1:D1=B1)*(S heet1!B2:D4)) This returns the % with your table laid out like below on sheet 1. Mike "wamz" wrote: A B C D 1 France Spain Italy 2 400010 6 % 1 % 3 % 3 400020 2 % 4 % 3 % 4 400040 1 % 2 % 3 % 5 . 6 . 7 . 8 I would like to put in the percentages into another sheet that looks like this: 400010 France 6% 400020 Spain 4% 400040 Italy 3% How can i do that. I've tried with if sentences but it does not work |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Insert values based on 2 criterias
thanks alot, that worked perfectly
"Mike H" wrote: Hi, I'm making the assumption you have 400010 France on your 'other sheet and it's just the percentage you want. If that's correct try this:- =SUMPRODUCT((Sheet1!A2:A4=A1)*(Sheet1!B1:D1=B1)*(S heet1!B2:D4)) This returns the % with your table laid out like below on sheet 1. Mike "wamz" wrote: A B C D 1 France Spain Italy 2 400010 6 % 1 % 3 % 3 400020 2 % 4 % 3 % 4 400040 1 % 2 % 3 % 5 . 6 . 7 . 8 I would like to put in the percentages into another sheet that looks like this: 400010 France 6% 400020 Spain 4% 400040 Italy 3% How can i do that. I've tried with if sentences but it does not work |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert Rows Based on values in another worksheet | Excel Discussion (Misc queries) | |||
| Counting instances based on two criterias | Excel Worksheet Functions | |||
| Delete rows based on multiple criterias | Excel Discussion (Misc queries) | |||
| Sum values depending on prefefined criterias | Excel Discussion (Misc queries) | |||
| Finding unique values with Criterias | Excel Discussion (Misc queries) | |||
Linear Mode
