sumif with summed columns
 

I want to use the sumif function to calculate my total profit for each month.
For example: I have a column titled "month" which I enter in the month I
earned the profit. Then I have rows for each item. In each row, I have an
"earned gross margin" cell which is the total revenue (what I billed the
customer) minus the total pay column (what I paid for it, which is a
combination of summed cells).

When i try to do a sumif as: =SUMF(AG2:AG1999, "Sept", AD2:AD1999) - it
only comes up with $40.90 and it should be $8395.87.

AG2: AG1999 is the column that I have the month in, in this case only "Sept".
AD2:AD19999 is the earned gross margin column, with each cell for example
row 2: =sum(AC2-W2), with AC2 the total revenue & W2 as the total pay.

What am i doing wrong?

sumif with summed columns
 

There could be at least a couple of problems.

You could have different strings in AG:
" Sept", "Sept ", "Sept.", " Sept ", "Sep"
would all cause trouble.

If you had a real date in that range, but changed the number format to show just
the month (or month abbreviation), then this would be a problem.

The second problem could be that the values in AD look like numbers, but could
really be text. But since you're using formulas in AD, this doesn't sound like
it's possible.

If you do:
=count(AD2:AD1999)
and
=counta(ad2:Ad1999)
do you get the same number?

The top one counts the number of cells with numbers. The bottom counts the
number of cells with anything in them.

And one more thing...

Any chance your data has both positive and negative values and the results shown
are really ok?

quinn111 wrote:

I want to use the sumif function to calculate my total profit for each month.
For example: I have a column titled "month" which I enter in the month I
earned the profit. Then I have rows for each item. In each row, I have an
"earned gross margin" cell which is the total revenue (what I billed the
customer) minus the total pay column (what I paid for it, which is a
combination of summed cells).

When i try to do a sumif as: =SUMF(AG2:AG1999, "Sept", AD2:AD1999) - it
only comes up with $40.90 and it should be $8395.87.

AG2: AG1999 is the column that I have the month in, in this case only "Sept".
AD2:AD19999 is the earned gross margin column, with each cell for example
row 2: =sum(AC2-W2), with AC2 the total revenue & W2 as the total pay.

What am i doing wrong?

--

Dave Peterson

sumif with summed columns
 

ps. Remember to make sure you're in automatic calculation mode.

In xl2003 menus, it's:
tools|options|calculation tab

Dave Peterson wrote:

There could be at least a couple of problems.

You could have different strings in AG:
" Sept", "Sept ", "Sept.", " Sept ", "Sep"
would all cause trouble.

If you had a real date in that range, but changed the number format to show just
the month (or month abbreviation), then this would be a problem.

The second problem could be that the values in AD look like numbers, but could
really be text. But since you're using formulas in AD, this doesn't sound like
it's possible.

If you do:
=count(AD2:AD1999)
and
=counta(ad2:Ad1999)
do you get the same number?

The top one counts the number of cells with numbers. The bottom counts the
number of cells with anything in them.

And one more thing...

Any chance your data has both positive and negative values and the results shown
are really ok?

quinn111 wrote:

I want to use the sumif function to calculate my total profit for each month.
For example: I have a column titled "month" which I enter in the month I
earned the profit. Then I have rows for each item. In each row, I have an
"earned gross margin" cell which is the total revenue (what I billed the
customer) minus the total pay column (what I paid for it, which is a
combination of summed cells).

When i try to do a sumif as: =SUMF(AG2:AG1999, "Sept", AD2:AD1999) - it
only comes up with $40.90 and it should be $8395.87.

AG2: AG1999 is the column that I have the month in, in this case only "Sept".
AD2:AD19999 is the earned gross margin column, with each cell for example
row 2: =sum(AC2-W2), with AC2 the total revenue & W2 as the total pay.

What am i doing wrong?

--

Dave Peterson

--

Dave Peterson

sumif with summed columns
 

Thank you Dave - it was the Tools/Options/Calculations tab.
I wouldn't have thought to look there.
Thanks so much for the help. Hope you have a very good day.

"Dave Peterson" wrote:

ps. Remember to make sure you're in automatic calculation mode.

In xl2003 menus, it's:
tools|options|calculation tab

Dave Peterson wrote:

There could be at least a couple of problems.

You could have different strings in AG:
" Sept", "Sept ", "Sept.", " Sept ", "Sep"
would all cause trouble.

If you had a real date in that range, but changed the number format to show just
the month (or month abbreviation), then this would be a problem.

The second problem could be that the values in AD look like numbers, but could
really be text. But since you're using formulas in AD, this doesn't sound like
it's possible.

If you do:
=count(AD2:AD1999)
and
=counta(ad2:Ad1999)
do you get the same number?

The top one counts the number of cells with numbers. The bottom counts the
number of cells with anything in them.

And one more thing...

Any chance your data has both positive and negative values and the results shown
are really ok?

quinn111 wrote:

I want to use the sumif function to calculate my total profit for each month.
For example: I have a column titled "month" which I enter in the month I
earned the profit. Then I have rows for each item. In each row, I have an
"earned gross margin" cell which is the total revenue (what I billed the
customer) minus the total pay column (what I paid for it, which is a
combination of summed cells).

When i try to do a sumif as: =SUMF(AG2:AG1999, "Sept", AD2:AD1999) - it
only comes up with $40.90 and it should be $8395.87.

AG2: AG1999 is the column that I have the month in, in this case only "Sept".
AD2:AD19999 is the earned gross margin column, with each cell for example
row 2: =sum(AC2-W2), with AC2 the total revenue & W2 as the total pay.

What am i doing wrong?

--

Dave Peterson

--

Dave Peterson