iCloud doc: https://www.icloud.com/numbers/07ZgsGu9ieQU3ClW19zsnjFXg#Sales_Dash_v5_template_work_copy
Issue: Unable to return the correct value in red shaded cells D6 and D7.
First Tab " Formula issue"
Table name: T Leverone MTD v5
Source data: Rep Expense table [in Rep Expense tab
Grateful for the pointing out the flaw in my SUMIFS calc formula
I think the formula in D6 is correctly returning a value of 0. There are no Job: rep expenses between 12/29 and 1/4.
The formula in D7 seems to have an incorrect reference to a column, shown here in bold:
=SUMIFS(Rep Expense::$F,$J,$A$1,Rep Expense::$A,">="&D$4,Rep Expense::$A,"<="&D$5,Rep Expense::$E,"yes",Rep Expense::$C,"Job:*",$K,$A$4)+SUMIFS(Rep Expense::$F,$J,$A$1,Rep Expense::$A,"<"&$D$4,Rep Expense::$E,"yes",Rep Expense::$C,"Job:*",$K,$A$4)
In general you may find it more efficient to avoid such long formulas. They're much easier to debug if you break them up into separate cells.
SG
I think the formula in D6 is correctly returning a value of 0. There are no Job: rep expenses between 12/29 and 1/4.
The formula in D7 seems to have an incorrect reference to a column, shown here in bold:
=SUMIFS(Rep Expense::$F,$J,$A$1,Rep Expense::$A,">="&D$4,Rep Expense::$A,"<="&D$5,Rep Expense::$E,"yes",Rep Expense::$C,"Job:*",$K,$A$4)+SUMIFS(Rep Expense::$F,$J,$A$1,Rep Expense::$A,"<"&$D$4,Rep Expense::$E,"yes",Rep Expense::$C,"Job:*",$K,$A$4)
In general you may find it more efficient to avoid such long formulas. They're much easier to debug if you break them up into separate cells.
SG
So sorry, I didn't mean to hit the solved button.
Re: There are no Job: rep expenses between 12/29 and 1/4.
There are 2 job expenses: 1/2/19 Job: Mileage $38.86 and 1/3/19 Job: Mileages for $24.94 in the area shaded yellow
Had a second look after a few hours.. It looks different to me know. 😀
In general I find the structure too complicated to follow and the formulas too long to be able to debug easily. Perhaps you could consider simplifying the structure.
Also beware of "dates." Even if you hide the time part they are always date-time strings, which can cause unexpected results in SUMIFS.
Sorry can't be of more help in debugging this.
SG
Re the formula in D7. So sorry - I have re checked that formula countless times. Are you saying that I have $J or need $J? Regardless, there is no $J. Where are you seeing $J? Thank you.
SUMIFS formula help - unable to correct calc
