Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Formula not woring

I can't get the formula in line 14 to work.  I copied it from line another like, added the title from A14 onto the list, and entered data into the detail page.  It worked in the first column, so I dragged it out to the end of the year and it's only working in 2 other fields.

I don't get it, because I don't really understand the formula.Master-Budget-row-14.xlsm
0
mikecox_
Asked:
mikecox_
  • 2
1 Solution
 
SteveCommented:
When copying the formula... do not drag it or the table column references will change...

To copy it sideways... right click... copy... then select the destination cell and right click paste special formula.

This will maintain the table refs.

The formula can also be shortened to:
=SUMIFS(tbl_Details[Amount],tbl_Details[Account],Master!$A14,tbl_Details[Date],">="&Master!C$1,tbl_Details[Date],"<="&EOMONTH(Master!C$1,0))

As using [ALL] just includes the header row too which is not necessary for the SUMIFS to Work

To understand the formula:
it will...
Sum up the column 'Amount' in table 'tbl_Details'
=SUMIFS(tbl_Details[Amount] ,
Where the column 'Account' in table 'tbl_Details'
tbl_Details[Account],
Equals the value of A14
$A14,
Where the column 'Date' in table 'tbl_Details'
tbl_Details[Date],
Is greater than or equal to C1
">="&C$1,
Where the column 'Date' in table 'tbl_Details'
tbl_Details[Date],
Is less than or equal to the end of month of the date in C1
"<="&EOMONTH(Master!C$1,0))
Master-Budget-row-14.xlsm
0
 
mikecox_Author Commented:
Perfect!  Thanks for explaining the formula and for helping me understand why it wasn't working, and for fixing it for me!
0
 
SteveCommented:
Always happy to help where we can :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now