software

Should be something like
=sumproduct(--(Sheet1!$a$10:$a$120=b$1,--(Sheet1!$b$10:$b$120=$a2),Sheet1!$c$10:$c$120))quot;rbquot; wrote:

gt; The date is a real date on the sheet. On the sheet that I'm trying to use
gt; the formula on, I wanted it to reference say quot;$a$3quot; so that the formulat
gt; could be copied down to do the same for all accounts, then to the next column
gt; for the next month. IE
gt; 01/31/06 02/28/06
gt; 102000 quot;formulaquot; quot;formulaquot;
gt; 102500 quot;formulaquot; quot;formulaquot;
gt;
gt; And have the account (B:10:b120=$a10) using a cell reference instead of
gt; having to type in each account on a different line.
gt;
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt; gt; Sumproduct should do, but you have to be sure that the test you're using for
gt; gt; the date has the same data type and value as what's in the table. If the
gt; gt; table has real dates (not text strings that look like dates), then
gt; gt; date(2006,1,31) should match, whereas 1/31/2006 would not.
gt; gt; So, for example,
gt; gt; =sumproduct(--(a1:a10=date(2006,1,31),--(b1:b10=100200),c1:c10))
gt; gt; (BTW, if the 'account' field is actually a string, then enclose it in quotes
gt; gt; in the formula).
gt; gt;
gt; gt; quot;rbquot; wrote:
gt; gt;
gt; gt; gt; I have the following info on one tab:
gt; gt; gt; Date Acct Amt
gt; gt; gt; 1/31/20061002001,000.00
gt; gt; gt; 1/31/2006101000-1,891,715.85
gt; gt; gt; 1/31/200610300044,013.05
gt; gt; gt; 1/31/20061035000
gt; gt; gt; 1/31/2006105000612,999.69
gt; gt; gt; 1/31/20061100007,903,395.07
gt; gt; gt; 1/31/20061150005,146,140.11
gt; gt; gt; 1/31/20061155002,810.43
gt; gt; gt; 1/31/2006116000-11,389.91
gt; gt; gt;
gt; gt; gt; On another sheet I'm trying to sum by acct by month in different columns. I
gt; gt; gt; have tried sumif and apparently that can only handle 1 criteria. I've tried
gt; gt; gt; sumproduct and can't get that to work either. Is there a way to do this?
gt; gt; gt;
gt; gt; gt;

THANKS SO MUCH! I finally got it to work!

quot;bpeltzerquot; wrote:

gt; Should be something like
gt; =sumproduct(--(Sheet1!$a$10:$a$120=b$1,--(Sheet1!$b$10:$b$120=$a2),Sheet1!$c$10:$c$120))
gt;
gt;
gt; quot;rbquot; wrote:
gt;
gt; gt; The date is a real date on the sheet. On the sheet that I'm trying to use
gt; gt; the formula on, I wanted it to reference say quot;$a$3quot; so that the formulat
gt; gt; could be copied down to do the same for all accounts, then to the next column
gt; gt; for the next month. IE
gt; gt; 01/31/06 02/28/06
gt; gt; 102000 quot;formulaquot; quot;formulaquot;
gt; gt; 102500 quot;formulaquot; quot;formulaquot;
gt; gt;
gt; gt; And have the account (B:10:b120=$a10) using a cell reference instead of
gt; gt; having to type in each account on a different line.
gt; gt;
gt; gt;
gt; gt; quot;bpeltzerquot; wrote:
gt; gt;
gt; gt; gt; Sumproduct should do, but you have to be sure that the test you're using for
gt; gt; gt; the date has the same data type and value as what's in the table. If the
gt; gt; gt; table has real dates (not text strings that look like dates), then
gt; gt; gt; date(2006,1,31) should match, whereas 1/31/2006 would not.
gt; gt; gt; So, for example,
gt; gt; gt; =sumproduct(--(a1:a10=date(2006,1,31),--(b1:b10=100200),c1:c10))
gt; gt; gt; (BTW, if the 'account' field is actually a string, then enclose it in quotes
gt; gt; gt; in the formula).
gt; gt; gt;
gt; gt; gt; quot;rbquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have the following info on one tab:
gt; gt; gt; gt; Date Acct Amt
gt; gt; gt; gt; 1/31/20061002001,000.00
gt; gt; gt; gt; 1/31/2006101000-1,891,715.85
gt; gt; gt; gt; 1/31/200610300044,013.05
gt; gt; gt; gt; 1/31/20061035000
gt; gt; gt; gt; 1/31/2006105000612,999.69
gt; gt; gt; gt; 1/31/20061100007,903,395.07
gt; gt; gt; gt; 1/31/20061150005,146,140.11
gt; gt; gt; gt; 1/31/20061155002,810.43
gt; gt; gt; gt; 1/31/2006116000-11,389.91
gt; gt; gt; gt;
gt; gt; gt; gt; On another sheet I'm trying to sum by acct by month in different columns. I
gt; gt; gt; gt; have tried sumif and apparently that can only handle 1 criteria. I've tried
gt; gt; gt; gt; sumproduct and can't get that to work either. Is there a way to do this?
gt; gt; gt; gt;
gt; gt; gt; gt;