SUMIFS with Multiple criteria including UNIQUE value

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

  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.

[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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Welcome to Mr Excel forum

Maybe something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A
[/td][td="bgcolor: #DCE6F1"]
B
[/td][td="bgcolor: #DCE6F1"]
C
[/td][td="bgcolor: #DCE6F1"]
D
[/td][td="bgcolor: #DCE6F1"]
E
[/td][td="bgcolor: #DCE6F1"]
F
[/td][td="bgcolor: #DCE6F1"]
G
[/td][td="bgcolor: #DCE6F1"]
H
[/td][td="bgcolor: #DCE6F1"]
I
[/td][td="bgcolor: #DCE6F1"]
J
[/td][td="bgcolor: #DCE6F1"]
K
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1
[/td][td]
Date​
[/td][td]
Person​
[/td][td]
Type​
[/td][td]
Reference​
[/td][td]
Value​
[/td][td][/td][td]
Person​
[/td][td]
Type​
[/td][td]
StartDate​
[/td][td]
EndDate​
[/td][td]
Result​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2
[/td][td]
05/07/2018​
[/td][td]
Adviser​
[/td][td]
Initial​
[/td][td]
AI0001​
[/td][td]
10​
[/td][td][/td][td]
Adviser​
[/td][td]
Initial​
[/td][td]
01/07/2018​
[/td][td]
31/07/2018​
[/td][td]
40​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3
[/td][td]
10/07/2018​
[/td][td]
Adviser​
[/td][td]
Initial​
[/td][td]
AI0001​
[/td][td]
10​
[/td][td][/td][td][/td][td]
Ad-Hoc​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4
[/td][td]
09/07/2018​
[/td][td]
Adviser​
[/td][td]
Ongoing​
[/td][td]
AI0002​
[/td][td]
20​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5
[/td][td]
05/07/2018​
[/td][td]
Client​
[/td][td]
Initial​
[/td][td]
AI0003​
[/td][td]
50​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6
[/td][td]
21/07/2018​
[/td][td]
Adviser​
[/td][td]
Ad-Hoc​
[/td][td]
AI0004​
[/td][td]
30​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7
[/td][td]
25/07/2018​
[/td][td]
Adviser​
[/td][td]
Initial​
[/td][td]
AI0004​
[/td][td]
30​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top