#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 43
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 133
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 43
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find specific value in array of array formula DzednConfsd Excel Worksheet Functions 2 January 13th 09 06:19 AM
Need help with formula (IF or perhaps array) Marty New Users to Excel 2 June 1st 08 04:22 PM
meaning of : IF(Switch; Average(array A, array B); array A) DXAT Excel Worksheet Functions 1 October 24th 06 06:11 PM
Array formula JR Excel Discussion (Misc queries) 1 December 20th 05 08:03 PM
Array Formula - using LEFT("text",4) in formula Andrew L via OfficeKB.com Excel Worksheet Functions 2 August 1st 05 02:36 PM


All times are GMT +1. The time now is 08:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"