need help with conditional SUMIF

[okcsteve]

I have a four-column database with 1252 records, where column A denotes dates, column B denotes quantity, column C denotes unit price, and column D denotes total sale (= column B X column C).

I need to sum all entries in column D that correspond to all entries in column A within a particular month.

In other words, I need to sum total sales for the month of January, February, etc in successive rows. Some months contain many records, where other months contain very few.

See cells D1257 thru D1262

I tried using the =SUMIFS function but that did not work.

Please HELP!

Thank you!

[FDibbins]

Try this, copied down as needed...
=SUMIFS($D$2:$D$1253,$A$2:$A$1253,">="&A1257,$A$2:$A$1253,"<"&EDATE(A1257,1))

[:) Sixthsense :)]

Pivot Table is the easiest method.

[salim ali]

See Please this file
using for this formula 1==>> jan 2===> Feb etc...

Please Login or Register  to view this content.

[FDibbins]

See Please this file

That is a very good way to do this, except that you are hard-coding the month - consider using the same approach to extract the month from then criteria dates?

[CARACALLA]

	G	H
1	Jan-19	77810
2	Feb-19	11736
3	Mar-19	48684
4	Apr-19	21934
5	May-19	49726
6	Jun-19	19765
7	Jul-19	17336
8	Aug-19	0
9	Sep-19	0
10	Oct-19	0
11	Nov-19	0
12	Dec-19	0

H1=IF($G1<>"",SUMPRODUCT((MONTH($A$2:$A$2000)=MONTH($G1))*(YEAR($A$2:$A$2000)=YEAR($G1))*($D$2:$D$2000)*($A$2:$A$2000<>"")),"")

copy down

[FDibbins]

Just a quick side-note that SP's tend to be far less efficient that SUMIFS in cases like this, especially if the data set grows to be in the 1000's

[okcsteve]

Works like a charm! Thank you so much!

How do I now give you credit for solving the post? This part is never intuitive to me since I don't get on here often. Perhaps the site could be designed to make this easier?