Sumifs with multiple criteria does not contain

spycein · Sep 18, 2017

Hi Everyone,
I have a following data set

[TABLE="class: grid, width: 387"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Dealer Type[/TD]
[TD]State Name[/TD]
[TD] Sale Value[/TD]
[/TR]
[TR]
[TD]01-05-2017[/TD]
[TD]Registered[/TD]
[TD]Tamil Nadu[/TD]
[TD] 3,483.00[/TD]
[/TR]
[TR]
[TD]01-05-2017[/TD]
[TD]Un-Registered[/TD]
[TD]Uttar Pradesh[/TD]
[TD] 2,591.00[/TD]
[/TR]
[TR]
[TD]01-05-2017[/TD]
[TD]Un-Registered[/TD]
[TD]Maharastra[/TD]
[TD] 3,530.00[/TD]
[/TR]
[TR]
[TD]01-05-2017[/TD]
[TD]Un-Registered[/TD]
[TD]JAMMU AND KASHMIR[/TD]
[TD] 1,982.00[/TD]
[/TR]
[TR]
[TD]02-05-2017[/TD]
[TD]Un-Registered[/TD]
[TD]HIMACHAL PRADESH[/TD]
[TD] 4,599.00[/TD]
[/TR]
[TR]
[TD]03-05-2017[/TD]
[TD]Un-Registered[/TD]
[TD]PUNJAB[/TD]
[TD] 4,380.00[/TD]
[/TR]
[TR]
[TD]05-05-2017[/TD]
[TD]Un-Registered[/TD]
[TD]CHANDIGARH[/TD]
[TD] 1,238.00[/TD]
[/TR]
[TR]
[TD]02-05-2017[/TD]
[TD]Registered[/TD]
[TD]UTTARAKHAND[/TD]
[TD] 949.00[/TD]
[/TR]
[TR]
[TD]03-05-2017[/TD]
[TD]Registered[/TD]
[TD]HARYANA[/TD]
[TD] 2,774.00[/TD]
[/TR]
[TR]
[TD]05-05-2017[/TD]
[TD]Registered[/TD]
[TD]DELHI[/TD]
[TD] 4,975.00[/TD]
[/TR]
[TR]
[TD]02-05-2017[/TD]
[TD]Registered[/TD]
[TD]RAJASTHAN[/TD]
[TD] 4,766.00[/TD]
[/TR]
[TR]
[TD]03-05-2017[/TD]
[TD]Un-Registered[/TD]
[TD]UTTAR PRADESH[/TD]
[TD] 4,651.00[/TD]
[/TR]
[TR]
[TD]05-05-2017[/TD]
[TD]Registered[/TD]
[TD]BIHAR[/TD]
[TD] 1,218.00[/TD]
[/TR]
[TR]
[TD]02-05-2017[/TD]
[TD]Registered[/TD]
[TD]SIKKIM[/TD]
[TD] 4,689.00[/TD]
[/TR]
[TR]
[TD]03-05-2017[/TD]
[TD]Un-Registered[/TD]
[TD]ARUNACHAL PRADESH[/TD]
[TD] 1,168.00[/TD]
[/TR]
[TR]
[TD]05-05-2017[/TD]
[TD]Registered[/TD]
[TD]NAGALAND[/TD]
[TD] 3,328.00[/TD]
[/TR]
[TR]
[TD]02-05-2017[/TD]
[TD]Registered[/TD]
[TD]MANIPUR[/TD]
[TD] 821.00[/TD]
[/TR]
[TR]
[TD]03-05-2017[/TD]
[TD]Un-Registered[/TD]
[TD]MIZORAM[/TD]
[TD] 1,698.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 52,840.00[/TD]
[/TR]
</tbody>[/TABLE]

I am looking for a formula which would return the value as per the following table criteria.

[TABLE="class: grid, width: 396, align: left"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Total Sale[/TD]
[TD]Uttar Pradesh[/TD]
[TD]Tamil Nadu[/TD]
[TD]Rest of India[/TD]
[/TR]
[TR]
[TD]01-05-2017[/TD]
[TD] 11,586.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02-05-2017[/TD]
[TD] 15,824.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03-05-2017[/TD]
[TD] 14,671.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05-05-2017[/TD]
[TD] 10,759.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] 52,840.00[/TD]
[TD][/TD]
[TD] -[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much in advance.

Regards

Shib

MARZIOTULLIO · Sep 18, 2017

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]
[TH][/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]

[/TH]
[TH]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]

[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]

[/TD]
[TD]Date[/TD]
[TD]Dealer Type[/TD]
[TD]State Name[/TD]
[TD]Sale Value[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=888888]#888888[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]

[/TD]
[TD]

1/5/2017

[/TD]
[TD]Registered[/TD]
[TD]Tamil Nadu[/TD]
[TD]

3,483.00