Formula help

[mrskidder]

Hello,

I am trying to put together a formula that will take into account cc type as well as a date range to total up a sum for a summary page.

[TABLE="width: 371"]
<tbody>[TR]
[TD]Card Type[/TD]
[TD] Settle Date[/TD]
[TD]Batch No.[/TD]
[TD]Items[/TD]
[TD]Sales Amount[/TD]
[/TR]
[TR]
[TD]MC[/TD]
[TD]04/28/2017[/TD]
[TD]10[/TD]
[TD]2.00[/TD]
[TD]$172.96[/TD]
[/TR]
[TR]
[TD]VI[/TD]
[TD]04/28/2017[/TD]
[TD]10[/TD]
[TD]1.00[/TD]
[TD]$83.15[/TD]
[/TR]
[TR]
[TD]VI[/TD]
[TD]04/29/2017[/TD]
[TD]11[/TD]
[TD]2.00[/TD]
[TD]$251.98[/TD]
[/TR]
[TR]
[TD]MC[/TD]
[TD]04/30/2017[/TD]
[TD]12[/TD]
[TD]1.00[/TD]
[TD]$41.53[/TD]
[/TR]
[TR]
[TD]VI[/TD]
[TD]04/30/2017[/TD]
[TD]12[/TD]
[TD]5.00[/TD]
[TD]$3,264.79[/TD]
[/TR]
[TR]
[TD]MC[/TD]
[TD]05/01/2017[/TD]
[TD]13[/TD]
[TD]2.00[/TD]
[TD]$3,470.33[/TD]
[/TR]
[TR]
[TD]VI[/TD]
[TD]05/01/2017[/TD]
[TD]13[/TD]
[TD]1.00[/TD]
[TD]$90.60[/TD]
[/TR]
[TR]
[TD]MC[/TD]
[TD]05/02/2017[/TD]
[TD]14[/TD]
[TD]1.00[/TD]
[TD]$167.94[/TD]
[/TR]
[TR]
[TD]VI[/TD]
[TD]05/02/2017[/TD]
[TD]14[/TD]
[TD]1.00[/TD]
[TD]$110.36[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]


What formula would I use if I want to only pull MC for dates between 4/28 & 5/1?

 

[63falcondude]

Try

=SUMIFS(E:E,A:A,"MC",B:B,">="&DATE(2017,4,28),B:B,"<="&DATE(2017,5,1))

 

[mrskidder]

Thank you. I forgot that I need another column with several options. For example:

[TABLE="width: 614"]
<tbody>[TR]
[TD]891512[/TD]
[TD]MC[/TD]
[TD]05/24/2017[/TD]
[TD]355[/TD]
[TD]2.00[/TD]
[TD]$140.00[/TD]
[TD]$.00[/TD]
[TD]$140.00[/TD]
[/TR]
[TR]
[TD]891512[/TD]
[TD]VI[/TD]
[TD]05/24/2017[/TD]
[TD]355[/TD]
[TD]11.00[/TD]
[TD]$430.00[/TD]
[TD]$.00[/TD]
[TD]$430.00[/TD]
[/TR]
[TR]
[TD]891509[/TD]
[TD]VI[/TD]
[TD]05/01/2017[/TD]
[TD]61[/TD]
[TD]1.00[/TD]
[TD]$30.00[/TD]
[TD]$.00[/TD]
[TD]$30.00[/TD]
[/TR]
[TR]
[TD]891425[/TD]
[TD]VI[/TD]
[TD]04/28/2017[/TD]
[TD]342[/TD]
[TD]1.00[/TD]
[TD]$2,360.00[/TD]
[TD]$.00[/TD]
[TD]$2,360.00[/TD]
[/TR]
[TR]
[TD]891425[/TD]
[TD]VI[/TD]
[TD]04/28/2017[/TD]
[TD]343[/TD]
[TD]1.00[/TD]
[TD]$437.00[/TD]
[TD]$.00[/TD]
[TD]$437.00[/TD]
[/TR]
[TR]
[TD]891425[/TD]
[TD]VI[/TD]
[TD]04/29/2017[/TD]
[TD]344[/TD]
[TD]1.00[/TD]
[TD]$1,614.40[/TD]
[TD]$.00[/TD]
[TD]$1,614.40[/TD]
[/TR]
[TR]
[TD]891425[/TD]
[TD]VI[/TD]
[TD]05/01/2017[/TD]
[TD]345[/TD]
[TD]1.00[/TD]
[TD]$175.00[/TD]
[TD]$.00[/TD]
[TD]$175.00[/TD]
[/TR]
[TR]
[TD]891425[/TD]
[TD]MC[/TD]
[TD]05/01/2017[/TD]
[TD]346[/TD]
[TD]1.00[/TD]
[TD]$.00[/TD]
[TD]-$6.50[/TD]
[TD]-$6.50[/TD]
[/TR]
[TR]
[TD]891425[/TD]
[TD]MC[/TD]
[TD]05/02/2017[/TD]
[TD]347[/TD]
[TD]1.00[/TD]
[TD]$2,295.50[/TD]
[TD]$.00[/TD]
[TD]$2,295.50[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]


I am needing to get the sum of VI & MC in 891425 & 891512 for the date range of 4/28-5/1/17. How much more does this complicate the formula?

 

[63falcondude]

I am needing to get the sum of VI & MC in 891425 & 891512 for the date range of 4/28-5/1/17. How much more does this complicate the formula?

Not much. This should do it:

Excel 2010
	A	B	C	D	E	F	G	H
1		Card Type	Settle Date	Batch No.	Items	Sales Amount		$4,586.40
2	891512	MC	5/24/2017	355	2	$140.00
3	891512	VI	5/24/2017	355	11	$430.00
4	891509	VI	5/1/2017	61	1	$30.00
5	891425	VI	4/28/2017	342	1	$2,360.00
6	891425	VI	4/28/2017	343	1	$437.00
7	891425	VI	4/29/2017	344	1	$1,614.40
8	891425	VI	5/1/2017	345	1	$175.00
9	891425	MC	5/1/2017	346	1	$0.00
10	891425	MC	5/2/2017	347	1	$2,295.50
Sheet1

Cell Formulas
Range	Formula
H1	=SUM(SUMIFS(F:F,B:B,{"VI","MC"},A:A,{891425,891512},C:C,">="&DATE(2017,4,28),C:C,"<="&DATE(2017,5,1)))

 

[mrskidder]

I'm not sure what I'm doing wrong but here is my formula. The 'Merchant Connect' is just the other sheet that the data is on. I am looking for the sum with column I being the dollar totals, column B I need 891415 & 891425, column C is VI & MC, column D is the date.

=SUM(SUMIFS('Merchant Connect'!I2:I5893,'Merchant Connect'!B:B,{891425,891512},'Merchant Connect'!C:C,{"VI","MC"},'Merchant Connect'!D:D,">="&DATE(2017,4,27),'Merchant Connect'!D:D,"<="&DATE(2017,5,3)))

All I'm getting is #VALUE so I'm unsure what I did wrong.

 

[Marcelo Branco]

@falcondude

I think your formula needs a small adjustment. Change the value in F9 from 0 to, say, 50.
Your formula returns the same value 4586,40, when it should return 4636,40

To fix it try (observe the ; instead of ,)
=SUM(SUMIFS(F:F,B:B,{"VI","MC"},A:A,{891425;891512},C:C,">="&DATE(2017,4,28),C:C,"<="&DATE(2017,5,1)))

Using two array criteria one must be a horizontal array (values separated by commas) and the other a vertical array (values separated by semicolons)

M.

 

[63falcondude]

=SUM(SUMIFS(F:F,B:B,{"VI","MC"},A:A,{891425;891512},C:C,">="&DATE(2017,4,28),C:C,"<="&DATE(2017,5,1)))

Using two array criteria one must be a horizontal array (values separated by commas) and the other a vertical array (values separated by semicolons)

Good catch!

I'm not sure what I'm doing wrong but here is my formula. The 'Merchant Connect' is just the other sheet that the data is on. I am looking for the sum with column I being the dollar totals, column B I need 891415 & 891425, column C is VI & MC, column D is the date.

=SUM(SUMIFS('Merchant Connect'!I2:I5893,'Merchant Connect'!B:B,{891425,891512},'Merchant Connect'!C:C,{"VI","MC"},'Merchant Connect'!D:D,">="&DATE(2017,4,27),'Merchant Connect'!D:D,"<="&DATE(2017,5,3)))

All I'm getting is #VALUE so I'm unsure what I did wrong.

Ranges in a SUMIFS formula must be the same dimensions. Also, take note of what Marcelo pointed out above and change the comma to a semicolon.