Hi chaps,
In worksheet 1 I have the below data. In worksheet 2 in cell A1 I have the date 01/01/2017.
Using the EOMONTH function I am able to refer to the date in A1 to sum the whole of January 2017 in my data.
I need to sum column E below, however I only want to sum column E once for every occasion where the contents of columns A C & D are duplicated.
For example, 03/01/2017 below should total 9 rather than 18 because A C & D are all the same. Where as 05/01/2017 should total 14 because even though column A is the same, C & D are different.
In addition to this, where column B is blank I don't want it summed at all, regardless of the contents of columns A C & D.
I've tried adapting various sum/frequency/sumproduct formulas with little progress so far.
Is my goal here actually achievable without the use of macros? Any help much appreciated
Thanks
<tbody>
</tbody>
In worksheet 1 I have the below data. In worksheet 2 in cell A1 I have the date 01/01/2017.
Using the EOMONTH function I am able to refer to the date in A1 to sum the whole of January 2017 in my data.
I need to sum column E below, however I only want to sum column E once for every occasion where the contents of columns A C & D are duplicated.
For example, 03/01/2017 below should total 9 rather than 18 because A C & D are all the same. Where as 05/01/2017 should total 14 because even though column A is the same, C & D are different.
In addition to this, where column B is blank I don't want it summed at all, regardless of the contents of columns A C & D.
I've tried adapting various sum/frequency/sumproduct formulas with little progress so far.
Is my goal here actually achievable without the use of macros? Any help much appreciated
Thanks
A | B | C | D | E |
01/01/2017 | Customer A | Teacher 1 | Course A | 9 |
01/01/2017 | Customer B | Teacher 1 | Course A | 9 |
01/01/2017 | Customer C | Teacher 1 | Course A | 9 |
03/01/2017 | Customer D | Teacher 2 | Course A | 9 |
03/01/2017 | Customer E | Teacher 2 | Course A | 9 |
05/01/2017 | Customer F | Teacher 3 | Course B | 5 |
05/01/2017 | Customer G | Teacher 4 | Course A | 9 |
08/01/2017 | Teacher 2 | Course C | 10 | |
09/01/2017 | Customer H | Teacher 1 | Course C | 10 |
<tbody>
</tbody>