software

I have 13 worksheets, one for every month plus a total worksheet. I am
looking for a simple way to sum different cells depending on the result in
another cell. For example on the 12 monthly worksheets they all have the
same columns. Number of sales, cost of sales, budget and so on. What I
want to do is look at each worksheet and check the Number of sales and only
accumulate the amounts in the budget cell only if Number of sales is greater
than zero and place that number in a cell on the total worksheet. I
appreciate any help.

One way

=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!A2quot;),quot;gt;0quot; ,INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!B2quot;)))

where G1:G11 holds each name of the sheets, or hard coded maybe

=SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;; quot;Aprquot;;quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot; '!A2quot;),quot;gt;0quot;,INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;;quot;Aprquot;; quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot;'!B2quot;) ))A2 is the cell you want to check and B2 the one you want to sum, both can be
changed into ranges

--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Daniellquot; gt; wrote in message
...
gt;I have 13 worksheets, one for every month plus a total worksheet. I am
gt; looking for a simple way to sum different cells depending on the result in
gt; another cell. For example on the 12 monthly worksheets they all have the
gt; same columns. Number of sales, cost of sales, budget and so on. What I
gt; want to do is look at each worksheet and check the Number of sales and
gt; only
gt; accumulate the amounts in the budget cell only if Number of sales is
gt; greater
gt; than zero and place that number in a cell on the total worksheet. I
gt; appreciate any help.
Thanks, I will give that a try.

quot;Peo Sjoblomquot; wrote:

gt; One way
gt;
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!A2quot;),quot;gt;0quot; ,INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!B2quot;)))
gt;
gt; where G1:G11 holds each name of the sheets, or hard coded maybe
gt;
gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;; quot;Aprquot;;quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot; '!A2quot;),quot;gt;0quot;,INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;;quot;Aprquot;; quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot;'!B2quot;) ))
gt;
gt;
gt; A2 is the cell you want to check and B2 the one you want to sum, both can be
gt; changed into ranges
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;Daniellquot; gt; wrote in message
gt; ...
gt; gt;I have 13 worksheets, one for every month plus a total worksheet. I am
gt; gt; looking for a simple way to sum different cells depending on the result in
gt; gt; another cell. For example on the 12 monthly worksheets they all have the
gt; gt; same columns. Number of sales, cost of sales, budget and so on. What I
gt; gt; want to do is look at each worksheet and check the Number of sales and
gt; gt; only
gt; gt; accumulate the amounts in the budget cell only if Number of sales is
gt; gt; greater
gt; gt; than zero and place that number in a cell on the total worksheet. I
gt; gt; appreciate any help.
gt;
gt;
gt;

I am having a little trouble getting this to work. I created each worksheet
and placed the formula in the cell and it gives me a #REF error. I hard
coded the worksheets into the formula with the same results

quot;Daniellquot; wrote:

gt; Thanks, I will give that a try.
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt; gt; One way
gt; gt;
gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!A2quot;),quot;gt;0quot; ,INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!B2quot;)))
gt; gt;
gt; gt; where G1:G11 holds each name of the sheets, or hard coded maybe
gt; gt;
gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;; quot;Aprquot;;quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot; '!A2quot;),quot;gt;0quot;,INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;;quot;Aprquot;; quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot;'!B2quot;) ))
gt; gt;
gt; gt;
gt; gt; A2 is the cell you want to check and B2 the one you want to sum, both can be
gt; gt; changed into ranges
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Regards,
gt; gt;
gt; gt; Peo Sjoblom
gt; gt;
gt; gt; nwexcelsolutions.com
gt; gt;
gt; gt;
gt; gt; quot;Daniellquot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I have 13 worksheets, one for every month plus a total worksheet. I am
gt; gt; gt; looking for a simple way to sum different cells depending on the result in
gt; gt; gt; another cell. For example on the 12 monthly worksheets they all have the
gt; gt; gt; same columns. Number of sales, cost of sales, budget and so on. What I
gt; gt; gt; want to do is look at each worksheet and check the Number of sales and
gt; gt; gt; only
gt; gt; gt; accumulate the amounts in the budget cell only if Number of sales is
gt; gt; gt; greater
gt; gt; gt; than zero and place that number in a cell on the total worksheet. I
gt; gt; gt; appreciate any help.
gt; gt;
gt; gt;
gt; gt;

You can download a working example here

nwexcelsolutions.com/Download...e sheets.xls

--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;Daniellquot; gt; wrote in message
...
gt;I am having a little trouble getting this to work. I created each
gt;worksheet
gt; and placed the formula in the cell and it gives me a #REF error. I hard
gt; coded the worksheets into the formula with the same results
gt;
gt; quot;Daniellquot; wrote:
gt;
gt;gt; Thanks, I will give that a try.
gt;gt;
gt;gt; quot;Peo Sjoblomquot; wrote:
gt;gt;
gt;gt; gt; One way
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!A2quot;),quot;gt;0quot; ,INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!B2quot;)))
gt;gt; gt;
gt;gt; gt; where G1:G11 holds each name of the sheets, or hard coded maybe
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;; quot;Aprquot;;quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot; '!A2quot;),quot;gt;0quot;,INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;;quot;Aprquot;; quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot;'!B2quot;) ))
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; A2 is the cell you want to check and B2 the one you want to sum, both
gt;gt; gt; can be
gt;gt; gt; changed into ranges
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt;
gt;gt; gt; Regards,
gt;gt; gt;
gt;gt; gt; Peo Sjoblom
gt;gt; gt;
gt;gt; gt; nwexcelsolutions.com
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Daniellquot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt; gt;I have 13 worksheets, one for every month plus a total worksheet. I
gt;gt; gt; gt;am
gt;gt; gt; gt; looking for a simple way to sum different cells depending on the
gt;gt; gt; gt; result in
gt;gt; gt; gt; another cell. For example on the 12 monthly worksheets they all have
gt;gt; gt; gt; the
gt;gt; gt; gt; same columns. Number of sales, cost of sales, budget and so on.
gt;gt; gt; gt; What I
gt;gt; gt; gt; want to do is look at each worksheet and check the Number of sales
gt;gt; gt; gt; and
gt;gt; gt; gt; only
gt;gt; gt; gt; accumulate the amounts in the budget cell only if Number of sales is
gt;gt; gt; gt; greater
gt;gt; gt; gt; than zero and place that number in a cell on the total worksheet. I
gt;gt; gt; gt; appreciate any help.
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
Thanks Peo It is amazing when you forget a ; Thanks

quot;Peo Sjoblomquot; wrote:

gt; You can download a working example here
gt;
gt; nwexcelsolutions.com/Download...e sheets.xls
gt;
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;Daniellquot; gt; wrote in message
gt; ...
gt; gt;I am having a little trouble getting this to work. I created each
gt; gt;worksheet
gt; gt; and placed the formula in the cell and it gives me a #REF error. I hard
gt; gt; coded the worksheets into the formula with the same results
gt; gt;
gt; gt; quot;Daniellquot; wrote:
gt; gt;
gt; gt;gt; Thanks, I will give that a try.
gt; gt;gt;
gt; gt;gt; quot;Peo Sjoblomquot; wrote:
gt; gt;gt;
gt; gt;gt; gt; One way
gt; gt;gt; gt;
gt; gt;gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!A2quot;),quot;gt;0quot; ,INDIRECT(quot;'quot;amp;G1:G11amp;quot;'!B2quot;)))
gt; gt;gt; gt;
gt; gt;gt; gt; where G1:G11 holds each name of the sheets, or hard coded maybe
gt; gt;gt; gt;
gt; gt;gt; gt; =SUMPRODUCT(SUMIF(INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;; quot;Aprquot;;quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot; '!A2quot;),quot;gt;0quot;,INDIRECT(quot;'quot;amp;{quot;Janquot;;quot;Febquot;;quot;Marquot;;quot;Aprquot;; quot;Mayquot;;quot;Junquot;;quot;Julquot;;quot;Augquot;;quot;Sepquot;;quot;Octquot;;quot;Novquot;}amp;quot;'!B2quot;) ))
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; A2 is the cell you want to check and B2 the one you want to sum, both
gt; gt;gt; gt; can be
gt; gt;gt; gt; changed into ranges
gt; gt;gt; gt;
gt; gt;gt; gt; --
gt; gt;gt; gt;
gt; gt;gt; gt; Regards,
gt; gt;gt; gt;
gt; gt;gt; gt; Peo Sjoblom
gt; gt;gt; gt;
gt; gt;gt; gt; nwexcelsolutions.com
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Daniellquot; gt; wrote in message
gt; gt;gt; gt; ...
gt; gt;gt; gt; gt;I have 13 worksheets, one for every month plus a total worksheet. I
gt; gt;gt; gt; gt;am
gt; gt;gt; gt; gt; looking for a simple way to sum different cells depending on the
gt; gt;gt; gt; gt; result in
gt; gt;gt; gt; gt; another cell. For example on the 12 monthly worksheets they all have
gt; gt;gt; gt; gt; the
gt; gt;gt; gt; gt; same columns. Number of sales, cost of sales, budget and so on.
gt; gt;gt; gt; gt; What I
gt; gt;gt; gt; gt; want to do is look at each worksheet and check the Number of sales
gt; gt;gt; gt; gt; and
gt; gt;gt; gt; gt; only
gt; gt;gt; gt; gt; accumulate the amounts in the budget cell only if Number of sales is
gt; gt;gt; gt; gt; greater
gt; gt;gt; gt; gt; than zero and place that number in a cell on the total worksheet. I
gt; gt;gt; gt; gt; appreciate any help.
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt;
gt;
gt;
gt;