SUMIF Formula

[craigg3]

Trying to find out how to get a SUMIF to work on the following information.

I have the following columns of data:
Col A Col B
Apr-19 $25
Apr-19 $50
Apr-19 $75
Apr-19 $25
Apr-19 $50
May-19 $100

I only want to SUMIF Anything that is Apr-19, so for the example above the answer would be $225. If May-19 was the first one is Col A, then I would only want to SUMIF May-19.
The problem I have is Apr-19 may really be 4/12/2019 for the first one, and then 4/15/19 for the second one. I need a way to SUMIF everything that is in the Month of April and Year 2019 (disregard day of month) and if May-19 was at the Top of the list then I would want to use SUMIF on everything for the Month of May and Year 2019.

 

[etaf]

what version of excel are you using

=SUMIFS(B2:B7,A2:A7,"> ="&DATE(YEAR(A2),MONTH(A2),DAY(1)),A2:A7,"< ="&EOMONTH(A2,0))

 

[processingclouds]

Hey there,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD][/TD]
[TD] Sum where A2 month matches others[/TD]
[/TR]
[TR]
[TD]4/1/19[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/5/19[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]=SUMPRODUCT(--(MONTH(A2:A7)=MONTH(A2))*B2:B7)[/TD]
[/TR]
[TR]
[TD]4/7/19[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]225[/TD]
[/TR]
[TR]
[TD]4/17/19[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/29/19[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/19/19[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

You can use [TABLE="width: 500"]
<tbody>[TR]
[TD]=SUMPRODUCT(--(MONTH(A2:A7)=MONTH(A2))*B2:B7)[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

This will take the month from first data cell in column A, and than sum all range where month is same.

Have a good day.

 

[ravisingh]

You coild use this array formula:

	A	B	C	D	E	F
Array formula

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]01-04-19[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]02-04-19[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]03-04-19[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"][/TD]
[TD="align: right"]226[/TD]

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

[TD="align: center"]7[/TD]
[TD="align: right"]04-04-19[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]05-04-19[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]15-05-19[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]D6[/TH]
[TD="align: left"]{=SUM((B4:B9)*(MONTH(A4:A9)=MONTH($A$4)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[ravisingh]

If you must use SUMIF create a helper column...

	A	B	C	D	E	F	G	H
SUMIF using helper column
Array formula

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]01-04-19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"]226[/TD]

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

[TD="align: center"]2[/TD]
[TD="align: right"]02-04-19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]03-04-19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"][/TD]
[TD="align: right"]226[/TD]

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

[TD="align: center"]4[/TD]
[TD="align: right"]04-04-19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]05-04-19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]15-05-19[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[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] "]E1[/TH]
[TD="align: left"]=SUMIF(B1:B6,MONTH(A1),C1:C6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=MONTH(A1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=MONTH(A2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=MONTH(A3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=MONTH(A4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=MONTH(A5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=MONTH(A6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]E3[/TH]
[TD="align: left"]{=SUM((C1:C6)*(MONTH(A1:A6)=MONTH($A$1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[ravisingh]

To display data on your posts you can see the posts at the Forum>About this Board section