| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adds City but not Panmure ( Combined Total )
Hello From Steved
My thinking is if I type {"City","Panmure"}, it would give me a combined total but it is only giving me City, Can you please steer me in the right direction. =IF(ISNA(VLOOKUP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) Thankyou. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adds City but not Panmure ( Combined Total )
You know what they say about a little knowledge Steve <bg
I think this is what you want =SUMPRODUCT((A2:A20={"City","Pamure"})*(F2:F20/24)) and format it as time -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Steved" wrote in message ... Hello From Steved My thinking is if I type {"City","Panmure"}, it would give me a combined total but it is only giving me City, Can you please steer me in the right direction. =IF(ISNA(VLOOKUP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) Thankyou. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adds City but not Panmure ( Combined Total )
Hello Bob from Steved
I should have my instructions a little clearer. I am adding information from sheet called "1st Week" and it is laid out into seven days, it is put in a summary Sheet Called "City-Panmure Combined". So my question is that if I take either City or Panmure it works as it should ( Singular ), how do I get it to add both City and Panmure. Thanks =IF(ISNA(VLOOKUP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) Thankyou. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adds City but not Panmure ( Combined Total )
Hello from Steved
The below formula adding 352:29+1092:32=1445:01, the below formula is returning 16045:49, yes I put * between the two formula to give an idea as to what I am trying to acheive. Thanks =IF(ISNA(VLOOKUP("Wiri",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Wiri",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1)*IF(IS NA(VLOOKUP("Papakura",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Papakura",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) "Steved" wrote: Hello Bob from Steved I should have my instructions a little clearer. I am adding information from sheet called "1st Week" and it is laid out into seven days, it is put in a summary Sheet Called "City-Panmure Combined". So my question is that if I take either City or Panmure it works as it should ( Singular ), how do I get it to add both City and Panmure. Thanks =IF(ISNA(VLOOKUP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) Thankyou. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adds City but not Panmure ( Combined Total )
Sorry, you have lost me completely mate. I am lost as to what City, Panmure,
Wiri, Papakura is all about, and what is where. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Steved" wrote in message ... Hello from Steved The below formula adding 352:29+1092:32=1445:01, the below formula is returning 16045:49, yes I put * between the two formula to give an idea as to what I am trying to acheive. Thanks =IF(ISNA(VLOOKUP("Wiri",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Wiri",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1)*IF(IS NA(VLOOKUP("Papakura",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Papakura",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) "Steved" wrote: Hello Bob from Steved I should have my instructions a little clearer. I am adding information from sheet called "1st Week" and it is laid out into seven days, it is put in a summary Sheet Called "City-Panmure Combined". So my question is that if I take either City or Panmure it works as it should ( Singular ), how do I get it to add both City and Panmure. Thanks =IF(ISNA(VLOOKUP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) Thankyou. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adds City but not Panmure ( Combined Total )
Sorry about the confusion
I should have my instructions a little clearer. I am adding information from sheet called "1st Week" and it is laid out into seven days, it is put in a summary Sheet Called "City-Panmure Combined". So my question is that if I take either City or Panmure it works as it should ( Singular ), how do I get it to add both City and Panmure. As for an example The below formula adding 352:29+1092:32=1445:01, the below formula is returning 16045:49, yes I put * between the two formula to give an idea as to what I am trying to acheive. Thanks =IF(ISNA(VLOOKUP("City",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("City",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1)*IF(IS NA(VLOOKUP("Panmure",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Panmure",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) "Bob Phillips" wrote: Sorry, you have lost me completely mate. I am lost as to what City, Panmure, Wiri, Papakura is all about, and what is where. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Steved" wrote in message ... Hello from Steved The below formula adding 352:29+1092:32=1445:01, the below formula is returning 16045:49, yes I put * between the two formula to give an idea as to what I am trying to acheive. Thanks =IF(ISNA(VLOOKUP("Wiri",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Wiri",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1)*IF(IS NA(VLOOKUP("Papakura",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Papakura",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) "Steved" wrote: Hello Bob from Steved I should have my instructions a little clearer. I am adding information from sheet called "1st Week" and it is laid out into seven days, it is put in a summary Sheet Called "City-Panmure Combined". So my question is that if I take either City or Panmure it works as it should ( Singular ), how do I get it to add both City and Panmure. Thanks =IF(ISNA(VLOOKUP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) Thankyou. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adds City but not Panmure ( Combined Total )
Please ignore I have posted another one
Thankyou. "Steved" wrote: Sorry about the confusion I should have my instructions a little clearer. I am adding information from sheet called "1st Week" and it is laid out into seven days, it is put in a summary Sheet Called "City-Panmure Combined". So my question is that if I take either City or Panmure it works as it should ( Singular ), how do I get it to add both City and Panmure. As for an example The below formula adding 352:29+1092:32=1445:01, the below formula is returning 16045:49, yes I put * between the two formula to give an idea as to what I am trying to acheive. Thanks =IF(ISNA(VLOOKUP("City",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("City",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1)*IF(IS NA(VLOOKUP("Panmure",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Panmure",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) "Bob Phillips" wrote: Sorry, you have lost me completely mate. I am lost as to what City, Panmure, Wiri, Papakura is all about, and what is where. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Steved" wrote in message ... Hello from Steved The below formula adding 352:29+1092:32=1445:01, the below formula is returning 16045:49, yes I put * between the two formula to give an idea as to what I am trying to acheive. Thanks =IF(ISNA(VLOOKUP("Wiri",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Wiri",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1)*IF(IS NA(VLOOKUP("Papakura",'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP("Papakura",'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) "Steved" wrote: Hello Bob from Steved I should have my instructions a little clearer. I am adding information from sheet called "1st Week" and it is laid out into seven days, it is put in a summary Sheet Called "City-Panmure Combined". So my question is that if I take either City or Panmure it works as it should ( Singular ), how do I get it to add both City and Panmure. Thanks =IF(ISNA(VLOOKUP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0)),"",SUBSTITUTE(TEXT(VLOOK UP({"City","Panmure"},'1st Week'!$A$1:$H$10000,6,0),"#.00"),".",":")*1) Thankyou. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Keeping formulas on running total and sorts | Excel Worksheet Functions | |||
| Aling multiple sets of data by header column | Excel Discussion (Misc queries) | |||
| Create List based on cell data | Excel Discussion (Misc queries) | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
| Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions | |||
Linear Mode
