SUMIFS with Multiple criteria including UNIQUE value

[AirwalkerUK]

Hi.

I have the following sample data,

I want to do Sum the Value in Column E if the following conditions are met

1. The date is within the month of July 2018
2. The Person is Adviser
3. The Type is Initial or Ad-Hoc
4. 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.

Row	Date	Person	Type	Reference	Value
1	05/07/2018	Adviser	Initial	AI0001	£10
2	10/07/2018	Adviser	Initial	AI0001	£10
3	09/07/2018	Adviser	Ongoing	AI0002	£20
4	05/07/2018	Client	Initial	AI0003	£50
5	21/07/2018	Adviser	Ad-Hoc	AI0004	£30
6	25/07/2018	Adviser	Initial	AI0004	£30

<tbody>
</tbody>

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

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Date​	Person​	Type​	Reference​	Value​		Person​	Type​	StartDate​	EndDate​	Result​
2​	05/07/2018​	Adviser​	Initial​	AI0001​	10​		Adviser​	Initial​	01/07/2018​	31/07/2018​	40​
3​	10/07/2018​	Adviser​	Initial​	AI0001​	10​			Ad-Hoc​
4​	09/07/2018​	Adviser​	Ongoing​	AI0002​	20​
5​	05/07/2018​	Client​	Initial​	AI0003​	50​
6​	21/07/2018​	Adviser​	Ad-Hoc​	AI0004​	30​
7​	25/07/2018​	Adviser​	Initial​	AI0004​	30​
8​

Criteria in columns G:J

Formula in K2
=SUMPRODUCT(--(B2:B7=G2),--ISNUMBER(MATCH(C2:C7,H2:H3,0)),--(A2:A7>=I2),--(A2:A7<=J2),--(MATCH(D2:D7,D2:D7,0)=ROW(D2:D7)-ROW(D2)+1),E2:E7)

Hope this helps

M.

 

[AirwalkerUK]

Hi.

Thanks for the reply.

Although this would work, the resulting value is on another sheet in a table with other information, so I want something where the criteria remains in the formula if possible.

Using your suggestion, I could replace the cell references with the criteria, but not sure how I could do this where I want an either/or on column C. i.e. either 'Initial' or 'Ad-Hoc'.

Any other ideas?

 

[Marcelo Branco]

Hard coding the criteria in the formula is not flexible - you'll need to change the formula manually every time you want to update the criteria

I suggest you use formulas (referring to the other sheet) to insert the criteria in G2:J2. Isn't that possible?

M.

 

[Marcelo Branco]

If you really need to hard code the criteria in the formula try something like

=SUMPRODUCT(--(B2:B7="Adviser"),--ISNUMBER(MATCH(C2:C7,{"Initial";"Ad-Hoc"},0)),--(A2:A7>=DATE(2018,7,1)),--(A2:A7<=DATE(2018,7,31)),--(MATCH(D2:D7,D2:D7,0)=ROW(D2:D7)-ROW(D2)+1),E2:E7)

M.

 

[AirwalkerUK]

Thanks. I'm not too bothered about the formula not being flexible.

I need to produce a monthly summary report for month by month and year to date, so I have 1 worksheet with the summary table (formulas in the cells where necessary) which then runs the formulas based on data in another sheet. The format of the data source/report will be the same each month, but includes the most recent months data so I just want to be able to copy and paste the data into the data sheet and have the formulas on the summary sheet calculate on the refreshed data.

I could have the criteria in the summary sheet, in hidden columns if that's the only way around it. I just though I would be able to add something into the formula I already had to just sum once where duplicates are found in column D.

You suggestion works, but didn't want to have to have the criteria outside the formula if it wasn't necessary.

 

[AirwalkerUK]

Thanks Marcelo. That looks to work.