V
V.Bistak
I was poking around this forum to try and find an answer to this but was
unable, and in the process, I found not many people seem to know about
this so here is a solution and a problem all at the same time.
I have seen a number of references on the board that said you can only
use one criteria when using SUMIF. The fact is, you can create a
multiple criteria sum if you let Excel build the formula by using an
add-in called conditional sum builder.
Here is an example:
{=SUM(IF($C$10:$C$39="SI",IF($M$10:$M$39>"""",$L$10:$L$39,0),0))}.
The problem is...this will not work if you type it in. It will only work
if Excel builds the formula and places it in the cell.
When Excel built this formula it put a pair of brackets at each end. Any
attempt to copy, alter or even click within the formula causes the
brackets to disappear and the formula to fail. If you type in the
formula and add the brackets yourself, it will not work. I can see the
formula in the cell, the formula works fine. But it will not exist
outside if its own build as you cannot duplicate the brackets. No..I'm
not nuts. I've sent these sheets to people and they can see the same
occurrence.
So, the question is: What are the brackets, why are the brackets making
the formula work and why wont it work without them. And once I find that
out, I can figure out why Excel will not let me build the formula using
a variable cell instead of hardcoding a value.
For the record, this was created using Office 2000. Cells C10 thru C39
are open fields that I list Accts Payable Vendor Codes. (in this
example, SI is a vendor but it can be any number of vendor codes). L10
thru L39 has the amount to pay. I can use SumIF to create a report that
scans the two lists and gives me a total, by each vendor code, of the
amount I owe each vendor. No problem there. Column M is simply a column
that holds an X to show that the particular bill was paid. So I wanted
to create a field that showed how much was left to pay each vendor.
Conditional Sum builder created the formula that said to scan column C
for a vendor code (in this case SI) then look at the list of amounts in
column L and total the amounts that did not have a check off next to it
in column M. Formula works fine, except that Excel will only build it if
I specify a fixed code. So I need a line for every possible vendor
showing the amount still due. I thought OK, Ill just modify the above
formula and substitute SI for a cell reference, except that is when I
found the bug that does not let me modify the built formula.
Strange but true.
Has anyone else seen this?
P.S. I'll be happy to send a sheet with it.
unable, and in the process, I found not many people seem to know about
this so here is a solution and a problem all at the same time.
I have seen a number of references on the board that said you can only
use one criteria when using SUMIF. The fact is, you can create a
multiple criteria sum if you let Excel build the formula by using an
add-in called conditional sum builder.
Here is an example:
{=SUM(IF($C$10:$C$39="SI",IF($M$10:$M$39>"""",$L$10:$L$39,0),0))}.
The problem is...this will not work if you type it in. It will only work
if Excel builds the formula and places it in the cell.
When Excel built this formula it put a pair of brackets at each end. Any
attempt to copy, alter or even click within the formula causes the
brackets to disappear and the formula to fail. If you type in the
formula and add the brackets yourself, it will not work. I can see the
formula in the cell, the formula works fine. But it will not exist
outside if its own build as you cannot duplicate the brackets. No..I'm
not nuts. I've sent these sheets to people and they can see the same
occurrence.
So, the question is: What are the brackets, why are the brackets making
the formula work and why wont it work without them. And once I find that
out, I can figure out why Excel will not let me build the formula using
a variable cell instead of hardcoding a value.
For the record, this was created using Office 2000. Cells C10 thru C39
are open fields that I list Accts Payable Vendor Codes. (in this
example, SI is a vendor but it can be any number of vendor codes). L10
thru L39 has the amount to pay. I can use SumIF to create a report that
scans the two lists and gives me a total, by each vendor code, of the
amount I owe each vendor. No problem there. Column M is simply a column
that holds an X to show that the particular bill was paid. So I wanted
to create a field that showed how much was left to pay each vendor.
Conditional Sum builder created the formula that said to scan column C
for a vendor code (in this case SI) then look at the list of amounts in
column L and total the amounts that did not have a check off next to it
in column M. Formula works fine, except that Excel will only build it if
I specify a fixed code. So I need a line for every possible vendor
showing the amount still due. I thought OK, Ill just modify the above
formula and substitute SI for a cell reference, except that is when I
found the bug that does not let me modify the built formula.
Strange but true.
Has anyone else seen this?
P.S. I'll be happy to send a sheet with it.