Sumifs with dates

[Sajilpulath]

Hi Mates,

I am sure that my question might have asked someone else in this forum itself and solved, i spend almost an hour by searching the answer but could't find, that is why this new thread.

The below is my data format, i need to get the SUM totals for each MONTH separately for A,B & C. (Note; I don't want to create 2 new cells with a start and end dates)

DATE COLLECT AMOUNT
1-Jan-19 A 10
2-Jan-19 B 15
3-Jan-19 C 20
4-Jan-19 A 25
5-Jan-19 A 30
6-Jan-19 B 35
7-Jan-19 B 40
8-Jan-19 C 45
1-Feb-19 C 5
2-Feb-19 A 10
3-Feb-19 B 15
4-Feb-19 C 20
5-Feb-19 C 25
6-Feb-19 C 30
7-Feb-19 B 35
8-Feb-19 A 40

[modytrane]

See attached.
Formula used:

[Sajilpulath]

Thank you so much...

its done

[Sajilpulath]

Hello Chief,

The formula is not working while i have exact dates in column A, as per the attached file all the dates are 19th of Jan and 19th of Feb, when i changed the dates the formula didn't took the amounts. any solution ?

[modytrane]

Dates are Jan 2019 and Feb 2019.
I don't know what your date format looks like. That's why it's better to attach a sample file so we can offer correct solution for your situation.

Please attach a sample file and then explain what you are expecting.

[modytrane]

Ok. See attached.

Formula used:

[Sajilpulath]

The bow is the exact of my data, ( i don't know how to attach an excel file)

DATE CODE AMOUNT
1-Jan-19 d 200.00
2-Jan-19 bc 100.00
5-Jan-19 d 250.00
20-Jan-19 bc 500.00
30-Jan-19 d 500.00
2-Feb-19 hd 100.00
9-Feb-19 rp 500.00
16-Feb-19 gf 300.00
27-Feb-19 hd 2,000.00
4-Mar-19 cb 50.00

My answer data should be like


CODE Jan-19 Feb-19 Mar-19 Apr-19
D
BC
HD
CB
RP
GF

[Sajilpulath]

Thank you for the update,let me check and update you soon.

[modytrane]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

[Sajilpulath]

Great, its worked..

Can't we do this with SUMIFS ?! (with a => 01-01-2019 & <=31-01-2019 , jest a question, i was trying for that, the current formula is very complex to me, never know why all that "--".

Anyway thank you so much.

[modytrane]

For SUMIFS to work you need to add helper columns [see attached].

I have added SUMIFS formulas in the highlighted cells.

[Sajilpulath]

Hi,

Thanks for showing how it works with SUMIFS.

Could you please explain me why all this '--' marks are important in the SUMPRODUCT formula, if possible.

Thank you again.

[JeteMc]

Linked is a good discussion on the -- (double unary) topic.
https://www.excelforum.com/excel-gen...hat-is-it.html