AirwalkerUK
New Member
- Joined
- Nov 13, 2018
- Messages
- 4
Hi.
I have the following sample data,
I want to do Sum the Value in Column E if the following conditions are met
Where all 4 conditions are met, I only want it to include the value in E once for the purpose of SUM. i.e in Row 1 & 2, all conditions are met, but I only want to include 1 £10, in the Sum. Likewise for rows 5 & 6, only want 1 £30 to be included.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Date[/TD]
[TD]Person[/TD]
[TD]Type[/TD]
[TD]Reference[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Initial[/TD]
[TD]AI0001
[/TD]
[TD]£10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Initial[/TD]
[TD]AI0001[/TD]
[TD]£10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]09/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Ongoing[/TD]
[TD]AI0002[/TD]
[TD]£20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]05/07/2018[/TD]
[TD]Client[/TD]
[TD]Initial[/TD]
[TD]AI0003[/TD]
[TD]£50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]21/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Ad-Hoc[/TD]
[TD]AI0004[/TD]
[TD]£30[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]25/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Initial[/TD]
[TD]AI0004[/TD]
[TD]£30[/TD]
[/TR]
</tbody>[/TABLE]
So Far I have -
=(SUMIFS('Data'!$E:$E,'Recd Data'!$A:$A,">30/06/2018",'Data'!$A:$A,"<01/08/2018",'Data'!$B:$B,"Adviser",'Data'!$C:$C,"*Initial*")+SUMIFS('Data'!$E:$E,'Data'!$A:$A,">30/06/2018",'Data'!$A:$A,"<01/08/2018",'Data'!$B:$B,"Adviser",'Data'!$C:$C,"Ad-Hoc"))
But as you can see it doesn't include a 'condition' for Column D (Reference) as I am unsure how to do this. The Reference numbers will always change and there could be 1000s of rows.
Help
I have the following sample data,
I want to do Sum the Value in Column E if the following conditions are met
- The date is within the month of July 2018
- The Person is Adviser
- The Type is Initial or Ad-Hoc
- The reference is Unique.
Where all 4 conditions are met, I only want it to include the value in E once for the purpose of SUM. i.e in Row 1 & 2, all conditions are met, but I only want to include 1 £10, in the Sum. Likewise for rows 5 & 6, only want 1 £30 to be included.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Date[/TD]
[TD]Person[/TD]
[TD]Type[/TD]
[TD]Reference[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Initial[/TD]
[TD]AI0001
[/TD]
[TD]£10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Initial[/TD]
[TD]AI0001[/TD]
[TD]£10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]09/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Ongoing[/TD]
[TD]AI0002[/TD]
[TD]£20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]05/07/2018[/TD]
[TD]Client[/TD]
[TD]Initial[/TD]
[TD]AI0003[/TD]
[TD]£50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]21/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Ad-Hoc[/TD]
[TD]AI0004[/TD]
[TD]£30[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]25/07/2018[/TD]
[TD]Adviser[/TD]
[TD]Initial[/TD]
[TD]AI0004[/TD]
[TD]£30[/TD]
[/TR]
</tbody>[/TABLE]
So Far I have -
=(SUMIFS('Data'!$E:$E,'Recd Data'!$A:$A,">30/06/2018",'Data'!$A:$A,"<01/08/2018",'Data'!$B:$B,"Adviser",'Data'!$C:$C,"*Initial*")+SUMIFS('Data'!$E:$E,'Data'!$A:$A,">30/06/2018",'Data'!$A:$A,"<01/08/2018",'Data'!$B:$B,"Adviser",'Data'!$C:$C,"Ad-Hoc"))
But as you can see it doesn't include a 'condition' for Column D (Reference) as I am unsure how to do this. The Reference numbers will always change and there could be 1000s of rows.
Help