SumIFS & COUNTIFS plus additional IFS to sum and only particular values.

Hello,

I am trying to replace a pivot that was summarizing payments by multiple criteria's using SUIMFS, with additional countifs to get rid of duplicates and account for unique situations.

Essentially what I am trying to achieve is get rid of this pivot table altogether and hardcode formulas in the sheet to replace to collapsed view the pivot was giving.

Pivot sum.PNG

I have replaced the Pivot sum with a sumifs on the worksheet.

The SUMIFS is working except when you drag the cell down it will duplicate the values, Example below is the $8,704.11 in AP13, and then again repeated $8704.11 in AP15. The true amount that month is $8704.11, the fill down formula just duplicated it.

So as you can see the formula I have in AR13 is working by putting an 'x" to unique value and "xx" for duplicates. And we can see it working as the $8,704.11 SUMIFS formula is only being moved to Column AS once, and not twice.

countifs of sumifs.PNG

if dup then do this.PNG



the second part is now I also need to account for the below issue with duplicate values. These are not truly duplicates as you can see it is the monthly sum. They just happen to be the same amount so the above formula is reading them like they are duplicates but they are not. So in this case I would need the all those $67.85 amounts in column AP to be put in column AS. But you can see that the formula called them out as "xx" so it wont pick them up as unique values. How do I work that in the formula?

not dups.PNG

Pivot sum.PNG

I modified the formula to add in additional logic and it seems to be working thus far

Please Login or Register  to view this content.