Sumifs range in number corresponding to the month

[silambarasan.J]

HI,

How do sumifs ranges in number corresponding to the month.

Data:

Document Date Plant Amount
01.04.2015 3100 5432.26
01.04.2015 3100 4203.28
01.04.2015 3100 915.61
01.04.2015 3100 22220.82
01.04.2015 3190 2655.81
01.04.2015 3190 254.67
01.04.2015 3700 8012.41
01.04.2015 3700 5521.92
01.04.2015 3700 8919.29

Req Format:

PLANT CODE
Month:Apr.15
A B C D

3100 3190 3700 58136.07

Am apply this formula

=SUMIFS('APRIL-15'!$C$2:$C$22294,'APRIL-15'!$B$2:$B$22294,REQFORMAT!$A3&REQFORMAT!B3&REQFORMAT!C3,'APRIL-15'!$A$2:$A$22294,">="&EOMONTH(REQFORMAT!$D$2,-1)+1,'APRIL-15'!$A$2:$A$22294,"<="&EOMONTH(REQFORMAT!$D$2,0))

value return is wrongly when range multiple.

please help me. file attached.

[JohnTopley]

When you have three plant codes are you trying to sum the values for Plant code 3100 + Plant Code 3190 + Plant code 3700?

Why are you concatenating A&BC against column B only???

[Ron Coderre]

Using your posted workbook

this formula, copied down, returns the sum for a particular month for up to 3 plant codes (listed in columns A through C)

Please Login or Register  to view this content.

This alternative formula returns the same values:

Please Login or Register  to view this content.

Is that something you can work with?

[samba_ravi]

=SUMPRODUCT(('APRIL-15'!$C$2:$C$22294)*(TEXT('APRIL-15'!$A$2:$A$22294,"MMYY")=TEXT($D$2,"mmyy"))*('APRIL-15'!$B$2:$B$22294=$A3:$C3))
or
=SUMPRODUCT(SUMIFS('APRIL-15'!$C$2:$C$22294,'APRIL-15'!$A$2:$A$22294,">"&EOMONTH($D$2,-1),'APRIL-15'!$A$2:$A$22294,"<="&EOMONTH($D$2,0),'APRIL-15'!$B$2:$B$22294,$A3:$C3))

[silambarasan.J]

JohnTopley Sir,

yes am trying to sum the values for the Plant code 3100+3190+3700 its called name of chennai.

so am concatenate to sum of plant against of month.file attached for your reference.

[silambarasan.J]

Ron Coderre Sir,

wow its a amassing thank you so much.value return is perfect.

[silambarasan.J]

nflsales Siva Sir.

Rocking Rocking.thank you so much.value return is perfect.

i can see you its very long time sir.

[Ron Coderre]

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)