Sumif, where the range is in date format (11/02/2018) etc, and the criteria is "FEB"

[RockandGrohl]

Any way to do this without a helper column?

Need to countif and sumif the following across two sheets:


Column C which has dates like "01/02/2019" in sheet "JG"

Against the criteria of C1 which is just "Jan" on sheet "Front"


Also in my criteria I'm trying to find value B4 in "Front" against the range "H:H" in "JG"


Have tried Sumproduct but it didn't work very well, any ideas? Obviously I can't do "month(C:C)" otherwise my woes would be over. Thanks!

 

[DanteAmor]

To count:

<b>Sheet: Front</b>

Excel Workbook
	A	B	C
1			jan
2
3
4		some	4
Sheet

<b>Sheet: JG</b>

Excel Workbook
	A	B	C	D	E	F	G	H
1
2
3			01/01/2019					some
4			01/01/2019					any
5			01/01/2019					some
6			01/01/2019					any
7			01/01/2019					some
8			01/01/2019					any
9			01/01/2019					some
Sheet

 

[RockandGrohl]

To count:



Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

This is working great, is there a way to SUMPRODUCT value? I now have a count of quantity, I need a total of the value according to those same criteria.

 

[DanteAmor]

This is working great, is there a way to SUMPRODUCT value? I now have a count of quantity, I need a total of the value according to those same criteria.

But in which column do you have the data to sum?

 

[RockandGrohl]

But in which column do you have the data to sum?

Column F


I wish Sumifs was more verbose, or able to handle date ranges and their variances without throwing a wobbly.

Thanks.

 

[FormR]

I wish Sumifs was more verbose, or able to handle date ranges and their variances without throwing a wobbly.

Hi, is the year important or does your data span mulitple years? Here is a SUMIFS() option that will sum the current year only.

Excel 2013/2016
	B	C
1		jan
2
3
4	some	19
Sheet1

Cell Formulas
Range	Formula
C4	=SUMIFS(JG!F:F,JG!C:C,">=1"&C1,JG!C:C,"<"&EOMONTH(1&C1,0)+1,JG!H:H,B4)

Excel 2013/2016
	C	D	E	F	G	H
3	01/01/2019			5		some
4	01/01/2019			5		any
5	01/01/2019			1		some
6	01/01/2019			5		any
7	01/01/2019			4		some
8	01/01/2019			8		any
9	01/01/2019			9		some
JG

 

[RockandGrohl]

Hi, is the year important or does your data span mulitple years? Here is a SUMIFS() option that will sum the current year only.

Excel 2013/2016

	B	C
	jan
some

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]19[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=SUMIFS(JG!F:F,JG!C:C,">=1"&C1,JG!C:C,"<"&EOMONTH(1&C1,0)+1,JG!H:H,B4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Excel 2013/2016

	C	D	E	F	G	H
some
any
some
any
some
any
some

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

</tbody>

JG​

Hi,

Thank you very much for this.

For the time being, it's 2019, but eventually we will start taking bookings for 2020 and these figures will need to be reported on. Does the End Of Month formula only count the current year then?

 

[FormR]

When you have data for Jan 2019 and Jan 2020 do you want a single formula that sums the January totals for both years?

If you do then SUMPRODUCT() is probably the way to go. If you want separate formulas to sum each specific year then that could be done with SUMIFS() but you would need to specify the year you want to sum.

 

[RockandGrohl]

When you have data for Jan 2019 and Jan 2020 do you want a single formula that sums the January totals for both years?

If you do then SUMPRODUCT() is probably the way to go. If you want separate formulas to sum each specific year then that could be done with SUMIFS() but you would need to specify the year you want to sum.

Guys, I appreciate all the help, but I'm scrapping this. The easiest solution is often the best, I've input the dates at the top of the columns as 01/01/2019 and onwards, then formatted them as "mmm" to show "Jan", "Feb", etc. Then just done a countif and sumif to say where the value is greater than or equal to Jan but less than Feb, show me the results.

The trouble with Sumproduct looking across two sheets is the crazy calculation time. I wrote a macro to enter it on a cell-by-cell basis then paste over with values but this still took an age.

So I've gone with the KISS method.

Thanks again for trying to make my dumb method work!