I've been banging my head against this DSUM formula that refuses to evaluate, and the key to the solution continues to elude me.
I have a table that generates a list of names, accounts and amounts. Sometimes a valid account can't be found so an #N/A appears in the account field. I'm trying to get a sum total of all the amounts for all the records that do have a valid account by using the formula
DSUM(Table2[[AMT]:[ACCT]],"AMT",M1:M2)
I have a formula in the criteria:
<TABLE style="TEXT-ALIGN: center" border=1><TBODY><TR><TD></TD><TD>M</TD></TR>
<TR><TD>1</TD><TD>ACCT</TD></TR><TR><TD>2</TD><TD>=not(isna(d2))</TD></TR></TBODY></TABLE>
And here's an example of the data in the table:
<TABLE style="TEXT-ALIGN: center" border=1><TBODY><TR><td></td><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR><td>1</td><TD>NAME</TD><TD>ID</TD><TD>AMT</TD><TD>ACCT</TD></TR><TR><td>2</td><TD>George Washington</TD><TD>001</TD><TD>$83.34</TD><TD>5019</TD></TR><TR><td>3</td><TD>John Adams</TD><TD>002</TD><TD>$166.68</TD><TD>5028</TD></TR><TR><td>4</td><TD>Thomas Jefferson</TD><TD>003</TD><TD>$41.67</TD><TD>5037</TD></TR>
<TR><td>5</td><TD>James Madison</TD><TD>004</TD><TD>$62.51</TD><TD>#N/A</TD></TR><TR><td>6</td><TD>James Monroe</TD><TD>005</TD><TD>$88.55</TD><TD>5054</TD></TR></TBODY></TABLE>
Any help would be deeply appreciated!
I have a table that generates a list of names, accounts and amounts. Sometimes a valid account can't be found so an #N/A appears in the account field. I'm trying to get a sum total of all the amounts for all the records that do have a valid account by using the formula
DSUM(Table2[[AMT]:[ACCT]],"AMT",M1:M2)
I have a formula in the criteria:
<TABLE style="TEXT-ALIGN: center" border=1><TBODY><TR><TD></TD><TD>M</TD></TR>
<TR><TD>1</TD><TD>ACCT</TD></TR><TR><TD>2</TD><TD>=not(isna(d2))</TD></TR></TBODY></TABLE>
And here's an example of the data in the table:
<TABLE style="TEXT-ALIGN: center" border=1><TBODY><TR><td></td><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR><td>1</td><TD>NAME</TD><TD>ID</TD><TD>AMT</TD><TD>ACCT</TD></TR><TR><td>2</td><TD>George Washington</TD><TD>001</TD><TD>$83.34</TD><TD>5019</TD></TR><TR><td>3</td><TD>John Adams</TD><TD>002</TD><TD>$166.68</TD><TD>5028</TD></TR><TR><td>4</td><TD>Thomas Jefferson</TD><TD>003</TD><TD>$41.67</TD><TD>5037</TD></TR>
<TR><td>5</td><TD>James Madison</TD><TD>004</TD><TD>$62.51</TD><TD>#N/A</TD></TR><TR><td>6</td><TD>James Monroe</TD><TD>005</TD><TD>$88.55</TD><TD>5054</TD></TR></TBODY></TABLE>
Any help would be deeply appreciated!