[AccessD] An Excel question

SUMPRODUCT() may do

Note operands should be numeric
so either use * between them to force multiplacation, or prefix each with --

Or, if you are after the ability to alter the entire range being viewed then 
there is the use of names
=SUMIF(Income!C3:C7, >0, Income!B3:B7)";
define name
incomevl= =Income!C3:C7
=SUMPRODUCT(--(Incomevl>0)*Incomevl)"

(NOT TESTED - NO EXCEL HERE)

For a better answer, please give more detail of your data setup and needs

JimB

----- Original Message ----- 
From: "Jim Lawrence" <accessd at shaw.ca>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Friday, April 26, 2013 10:59 PM
Subject: [AccessD] An Excel question


> Hi All:
>
> I have an Excel question, probably not too difficult but time is of the
> essence so if someone knows the answer, off the top, excellent.
>
> To pull an amount from an adjacent or cascading sheet, the code, in a 
> cell,
> would be similar to "=SUM(Income!D26)". This is a single amount from a 
> sheet
> named "Income" from one cell "D26". And a single cell criteria can easily
> set like: "=SUMIF(Income!C3:C7, >0, Income!B3:B7)"; range, criteria,
> sum_range.
>
> Question: Is there are way to pull a range of cells, given a criteria, 
> from
> an adjacent sheet...without having to do a major chunk of programming?
>
> I am looking to create a number of year worksheet which pull detail from 
> one
> master worksheet which has a 5 to 10 year spread of data. It needs to be
> totally automated.
>
> TIA
>
> Jim
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com