I am having trouble figuring the proper way to format a spreadsheet. This spreadsheet's data varies daily and I am looking for a way to sum totals from a column if it meets certain criteria from another column. The problem I get is that I have to paste the formula to every cell, because of the data varying daily. This causes a lot of duplicate entries.
I know there is a better way for me to do this, I just don't have the knowledge of it.
Can anyone help?
My first impression is you need your formula to be dynamic based off of those varying rows.
Can you provide more detail? What is the current formula you are using?
HTH
Regards, Jeff
If your SUMIF formula is something like:
=SUMIF(A:A,A1,B:B)
then you can add something like:
=IF(COUNTIF($A$1:$A1,$A1)=1,SUMIF($A:$A,$A1,$B:$B),"")
this only sums at first occurance of a duplicate in A?
Is that what you mean?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
=SUMIFS(H:H,F:F,F1,E:E,E1)
Here is the current formula. It is summing cells from column H, if anything in column F equals F1 and if anything in column E equals E1.
Book1.xlsx
Here is a sample.
Thank you for the sample...
What about in L1 copied down
=IF(E1&F1=E2&F2,"",J1)
I noticed you have blanks where there is one account, but with this formula it will give you those as well.
If you don't want the non-duplicates...
=IF(COUNTIF($E$1:$E$16,E1)>1,IF(E1&F1=E2&F2,"",J1),"")
Last edited by jeffreybrown; 05-29-2012 at 03:06 PM.
Are you using dates as the breakpoints? If so, the last row has a may 25th date, separate from other dates...?
Column J was generated just to show the duplicates that I am trying to avoid. That data is not normally in my report. I would like to sum the corresponding row from Column H everytime, the two criterion from column E and column F are met.
I hope this is making sense?
Here are 2 options, depending on if you want the show at first occurance:
=IF(COUNTIFS(F$1:F1,F1,E$1:E1,E1)=1,SUMIFS(H:H,F:F,F1,E:E,E1),"")
or last occurance,
=IF(AND(E2<>E1,F2<>F1),SUMIFS(H:H,F:F,F1,E:E,E1),"")
Thanks for your help on this. I am heading out of the office today but will keep in touch with what progress I find.
Thanks again for everyones help
see the file if it is okay
hello
Attachment 158625
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks