Say you’ve got a table like this:
| A | B | |
| 1 | Date | Transaction |
| 2 | 1-Jan | $ 1.20 |
| 3 | 1-Jan | $ 44.00 |
| 4 | 1-Jan | $ 2.50 |
| 5 | 2-Jan | $ 142.00 |
| 6 | 2-Jan | $ 23.00 |
| 7 | 2-Jan | $12,335.00 |
| 8 | 3-Jan | $ 5.00 |
| 9 | 3-Jan | $ 2,354.00 |
| 10 | 4-Jan | $ 65.00 |
| 11 | 6-Jan | $ 7.00 |
and you want a daily summary of the transactions, where the number of rows per day is not fixed. Tricky.
Using this forumula in cell C2:
=IF(A2=A3,”",SUMIF(A:A,A2,B:B))
and copying it down the page will work. To break it down:
- If A2=A3, then this is not the last entry for this date, so output an empty cell. (”")
- If A2!=A3, then this IS the last entry for this date, so search column A (A:A) for rows where the value is equal to A2 (ie, 1-Jan) and sum the values in column B (B:B) to output the answer:
| A | B | C | |
| 1 | Date | Transaction | Daily Total |
| 2 | 1-Jan | $ 1.20 | |
| 3 | 1-Jan | $ 44.00 | |
| 4 | 1-Jan | $ 2.50 | 47.7 |
| 5 | 2-Jan | $ 142.00 | |
| 6 | 2-Jan | $ 23.00 | |
| 7 | 2-Jan | $12,335.00 | 12500 |
| 8 | 3-Jan | $ 5.00 | |
| 9 | 3-Jan | $ 2,354.00 | 2359 |
| 10 | 4-Jan | $ 65.00 | 65 |
| 11 | 6-Jan | $ 7.00 | 7 |
One Comment
Hey! That’s pretty good! You just forgot to format your B & C column cells to finish off! But I like it very much! Well done!