Complex sumifs formula with multiple criteria

[korhan]

Hi everyone,

I am trying to implement a count formula using sumifs but I couldn't wrap my head around it.
I want to be able to count member depending on the code column. Anything less than or equal 3. Also, date will be another criteria.
Trickiest part is amount will be a criteria for counting the member. If the amount is negative, count member as negative and if positive just count ( add) as positive.
Any ideas?
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Code[/TD]
[TD]Member[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]123[/TD]
[TD]-$50[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]-$20[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]$25[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]-$100[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]$55[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]$23[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]$42[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]-$25[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]$25[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]2/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]$23[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

given the data provided why is the first member equal to 12?

 

[korhan]

These are just random numbers.
It is just a number. Think of it like you are counting a member for how many times they visited your store.

 

[VBA Geek]

So if I am understanding it correctly, Member number 6 for 02/01/2017 should give a count of 2 , whereas member number 9 for the same date should give a count of 0?

 

[korhan]

Look at it this way, on 02/01/2017 counting the member visits, I should get 30.
If the $ amount is negative count that visit as negative.
8 + 6 + 6 - 9 + 10 + 9.
Most of these visits left positive $ amounts so count them as positive. There is only one negative amount so subtract that from the total visits.

 

[VBA Geek]

should you not get 20 as the entry associated with 10 has a code of 5 which is > 3

 

[korhan]

Yes, you are right. I have missed that. It should be 20.
Thanks for correcting me.

 

[VBA Geek]

can you check if this does what you Want?

Excel 2013/2016
	A	B	C	D	E	F	G
1	Date	Code	Member	Amount
2	01/01/2017	1	12	200		02/01/2017	20
3	01/01/2017	1	2	100
4	01/01/2017	1	123	-50
5	01/01/2017	3	5	-20
6	01/01/2017	1	6	25
7	01/01/2017	4	7	-100
8	02/01/2017	3	8	55
9	02/01/2017	2	6	23
10	02/01/2017	1	6	42
11	02/01/2017	1	9	-25
12	02/01/2017	5	10	25
13	02/01/2017	3	9	23
Sheet3

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(SUMIFS($C$2:$C$13,$A$2:$A$13,F2,$B$2:$B$13,"<4",$D$2:$D$13,{"<0",">0"}),{-1,1})

 

[korhan]

Yes, that does it. Thanks a lot.
Couple of questions, how were you able to neatly copy paste your table into here?
Secondly, can you give a brief explanation on how this formula is able to do what I wanted.
I appreciate your time.

 

[VBA Geek]

how were you able to neatly copy paste your table into here?

download mrExcel HTML Maker:
https://onedrive.live.com/?id=8CFFDEC0CE27E813!189&cid=8CFFDEC0CE27E813

can you give a brief explanation on how this formula is able to do what I wanted

it is a simple sumif formula but that uses an array as a last argument to return in an array those with condition less than 0 and those more than 0. then sumproduct will multiply those less than 0 by -1 and those above 0 by 1 and sum them together, in fact doing a subtraction