Summing a range with conditions
 

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


"rb" wrote:

The date is a real date on the sheet. On the sheet that I'm trying to use
the formula on, I wanted it to reference say "$a$3" so that the formulat
could be copied down to do the same for all accounts, then to the next column
for the next month. IE
01/31/06 02/28/06
102000 "formula" "formula"
102500 "formula" "formula"

And have the account (B:10:b120=$a10) using a cell reference instead of
having to type in each account on a different line.


"bpeltzer" wrote:

Sumproduct should do, but you have to be sure that the test you're using for
the date has the same data type and value as what's in the table. If the
table has real dates (not text strings that look like dates), then
date(2006,1,31) should match, whereas 1/31/2006 would not.
So, for example,
=sumproduct(--(a1:a10=date(2006,1,31),--(b1:b10=100200),c1:c10))
(BTW, if the 'account' field is actually a string, then enclose it in quotes
in the formula).

"rb" wrote:

I have the following info on one tab:
Date Acct Amt
1/31/2006 100200 1,000.00
1/31/2006 101000 -1,891,715.85
1/31/2006 103000 44,013.05
1/31/2006 103500 0
1/31/2006 105000 612,999.69
1/31/2006 110000 7,903,395.07
1/31/2006 115000 5,146,140.11
1/31/2006 115500 2,810.43
1/31/2006 116000 -11,389.91

On another sheet I'm trying to sum by acct by month in different columns. I
have tried sumif and apparently that can only handle 1 criteria. I've tried
sumproduct and can't get that to work either. Is there a way to do this?

Summing a range with conditions
 

THANKS SO MUCH! I finally got it to work!

"bpeltzer" wrote:

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


"rb" wrote:

The date is a real date on the sheet. On the sheet that I'm trying to use
the formula on, I wanted it to reference say "$a$3" so that the formulat
could be copied down to do the same for all accounts, then to the next column
for the next month. IE
01/31/06 02/28/06
102000 "formula" "formula"
102500 "formula" "formula"

And have the account (B:10:b120=$a10) using a cell reference instead of
having to type in each account on a different line.


"bpeltzer" wrote:

Sumproduct should do, but you have to be sure that the test you're using for
the date has the same data type and value as what's in the table. If the
table has real dates (not text strings that look like dates), then
date(2006,1,31) should match, whereas 1/31/2006 would not.
So, for example,
=sumproduct(--(a1:a10=date(2006,1,31),--(b1:b10=100200),c1:c10))
(BTW, if the 'account' field is actually a string, then enclose it in quotes
in the formula).

"rb" wrote:

I have the following info on one tab:
Date Acct Amt
1/31/2006 100200 1,000.00
1/31/2006 101000 -1,891,715.85
1/31/2006 103000 44,013.05
1/31/2006 103500 0
1/31/2006 105000 612,999.69
1/31/2006 110000 7,903,395.07
1/31/2006 115000 5,146,140.11
1/31/2006 115500 2,810.43
1/31/2006 116000 -11,389.91

On another sheet I'm trying to sum by acct by month in different columns. I
have tried sumif and apparently that can only handle 1 criteria. I've tried
sumproduct and can't get that to work either. Is there a way to do this?