Array Formulae
 

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.

=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)

Array Formulae
 

Forget array formulas and use SUMPRODUCT
For details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html


Come back if more info needed
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Chris waller" wrote in message
...
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.

=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)

Array Formulae
 

Each $F$x:$F$x="" try to replace with $F$x:$F$x="*phrase*". Put phrase u want
to add in between asterix.
If there are more phrases (but still few) do it like:
=SUM((Sheet2!$C$3:$C$31="*phrase1*")*Sheet2!$G$3:$ G$31)+SUM((Sheet3!$C$3:$C$68="*phrase1*")*Sheet3!$ G$3:$G$68)+SUM((Sheet4!$C$3:$C$53="*phrase1*")*She et4!$G$3:$G$53)

+SUM((Sheet2!$C$3:$C$31="*phrase2*")*Sheet2!$G$3:$ G$31)+SUM((Sheet3!$C$3:$C$68="*phrase2*")*Sheet3!$ G$3:$G$68)+SUM((Sheet4!$C$3:$C$53="*phrase2*")*She et4!$G$3:$G$53)+...

so expand ur formula for several sets of different phrases

Click yes if this helps.

"Chris waller" wrote:

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.

=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)

Array Formulae
 

Of course, if u want still count for cells in F equalling "", then add my
formula to ur initial one, getting:
=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)

+SUM((Sheet2!$C$3:$C$31="*phrase1*")*Sheet2!$G$3:$ G$31)+SUM((Sheet3!$C$3:$C$68="*phrase1*")*Sheet3!$ G$3:$G$68)+SUM((Sheet4!$C$3:$C$53="*phrase1*")*She et4!$G$3:$G$53)

+SUM((Sheet2!$C$3:$C$31="*phrase2*")*Sheet2!$G$3:$ G$31)+SUM((Sheet3!$C$3:$C$68="*phrase2*")*Sheet3!$ G$3:$G$68)+SUM((Sheet4!$C$3:$C$53="*phrase2*")*She et4!$G$3:$G$53)+... and so on


"Alojz" wrote:

Each $F$x:$F$x="" try to replace with $F$x:$F$x="*phrase*". Put phrase u want
to add in between asterix.
If there are more phrases (but still few) do it like:
=SUM((Sheet2!$C$3:$C$31="*phrase1*")*Sheet2!$G$3:$ G$31)+SUM((Sheet3!$C$3:$C$68="*phrase1*")*Sheet3!$ G$3:$G$68)+SUM((Sheet4!$C$3:$C$53="*phrase1*")*She et4!$G$3:$G$53)

+SUM((Sheet2!$C$3:$C$31="*phrase2*")*Sheet2!$G$3:$ G$31)+SUM((Sheet3!$C$3:$C$68="*phrase2*")*Sheet3!$ G$3:$G$68)+SUM((Sheet4!$C$3:$C$53="*phrase2*")*She et4!$G$3:$G$53)+...

so expand ur formula for several sets of different phrases

Click yes if this helps.

"Chris waller" wrote:

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.

=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)