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,

Col A	Col B		Sum where A2 month matches others
4/1/19	25
4/5/19	50		=SUMPRODUCT(--(MONTH(A2:A7)=MONTH(A2))*B2:B7)
4/7/19	75		225
4/17/19	25
4/29/19	50
5/19/19	100

<tbody>
</tbody>

You can use

=SUMPRODUCT(--(MONTH(A2:A7)=MONTH(A2))*B2:B7)

<tbody>
</tbody>

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
4	01-04-19	25
5	02-04-19	50
6	03-04-19	76		226	Array formula
7	04-04-19	25
8	05-04-19	50
9	15-05-19	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Array Formulas Cell Formula D6 {=SUM((B4:B9)*(MONTH(A4:A9)=MONTH($A$4)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[ravisingh]

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

	A	B	C	D	E	F	G	H
1	01-04-19	4	25		226	SUMIF using helper column
2	02-04-19	4	50
3	03-04-19	4	76		226	Array formula
4	04-04-19	4	25
5	05-04-19	4	50
6	15-05-19	5	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula E1 =SUMIF(B1:B6,MONTH(A1),C1:C6) B1 =MONTH(A1) B2 =MONTH(A2) B3 =MONTH(A3) B4 =MONTH(A4) B5 =MONTH(A5) B6 =MONTH(A6) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E3 {=SUM((C1:C6)*(MONTH(A1:A6)=MONTH($A$1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[ravisingh]

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