| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula
I have an array formula that is summarising data from 3 spreadsheets,
however, I need to know how I can modify part of the formula so it will sum different data. If you look at the formula you will see that it is summarising the data where there is nothing in column F, however, I need to be able to modify the formula so that it will sum both cells that contain nothing and also if they contain certain phrases within a string of text. There are possibly a dozen different variations and the cell can also contain dates. I am not so bothered about the dates as these will all be different, it is only if the cells that contain phrases that I am trying to identify and summarise. TIA =SUM((Sheet2!$C$3:$C$31="HEO")*(Sheet2!$F$3:$F$31= "")*Sheet2!$G$3:$G$31)+SUM((Sheet3!$C$3:$C$68="HEO ")*(Sheet3!$F$3:$F$68="")*Sheet3!$G$3:$G$68)+SUM(( Sheet4!$C$3:$C$53="HEO")*(Sheet4!$F$3:$F$53="")*Sh eet4!$G$3:$G$53) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array Formula
Can you post a sample file on www.filedropper.com or another such site
? Daniel I have an array formula that is summarising data from 3 spreadsheets, however, I need to know how I can modify part of the formula so it will sum different data. If you look at the formula you will see that it is summarising the data where there is nothing in column F, however, I need to be able to modify the formula so that it will sum both cells that contain nothing and also if they contain certain phrases within a string of text. There are possibly a dozen different variations and the cell can also contain dates. I am not so bothered about the dates as these will all be different, it is only if the cells that contain phrases that I am trying to identify and summarise. TIA =SUM((Sheet2!$C$3:$C$31="HEO")*(Sheet2!$F$3:$F$31= "")*Sheet2!$G$3:$G$31)+SUM((Sheet3!$C$3:$C$68="HEO ")*(Sheet3!$F$3:$F$68="")*Sheet3!$G$3:$G$68)+SUM(( Sheet4!$C$3:$C$53="HEO")*(Sheet4!$F$3:$F$53="")*Sh eet4!$G$3:$G$53) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Array Formula
Daniel,
The file is at work, so it will have to wait until Monday. Thanks once again. "Daniel.C" wrote: Can you post a sample file on www.filedropper.com or another such site ? Daniel I have an array formula that is summarising data from 3 spreadsheets, however, I need to know how I can modify part of the formula so it will sum different data. If you look at the formula you will see that it is summarising the data where there is nothing in column F, however, I need to be able to modify the formula so that it will sum both cells that contain nothing and also if they contain certain phrases within a string of text. There are possibly a dozen different variations and the cell can also contain dates. I am not so bothered about the dates as these will all be different, it is only if the cells that contain phrases that I am trying to identify and summarise. TIA =SUM((Sheet2!$C$3:$C$31="HEO")*(Sheet2!$F$3:$F$31= "")*Sheet2!$G$3:$G$31)+SUM((Sheet3!$C$3:$C$68="HEO ")*(Sheet3!$F$3:$F$68="")*Sheet3!$G$3:$G$68)+SUM(( Sheet4!$C$3:$C$53="HEO")*(Sheet4!$F$3:$F$53="")*Sh eet4!$G$3:$G$53) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find specific value in array of array formula | Excel Worksheet Functions | |||
| Need help with formula (IF or perhaps array) | New Users to Excel | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Array formula | Excel Discussion (Misc queries) | |||
| Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions | |||
Linear Mode
