• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please help me in COUNTIFS and SUMPRODUCT [SOLVED]

B

bhasoriya

Member
Have tried had to get the result but not get yet. Please guide me what's wrong in my formula. Please check below given link for file.


http://rapidshare.com/files/1513561679/Monthly%20report.xlsx
 
Hi Bhasoriya,


Try this:


Code: 
=SUMPRODUCT((LA!$C$3:$C$44=$A4)*(TEXT(LA!$B$3:$B$44,"MMM, YY")=TEXT(LA!$B$29,"MMM, YY"))*1)


Regards,
 
Hello Faseeh,


There is something missing. when i change month in the sheet "Resource Monthly > Cell A1,

It is not changing data as per month. I have update this formula little bit as given below. Could you please check it once?


=SUMPRODUCT((LA!$C$3:$C$44=$A4)*(TEXT(LA!$B$3:$B$44,"MMM, YY")=TEXT(LA!$B$29,"MMM, YY"))*1)
 
Good day bhasoriya


Your dropdown data validation is doing what it is supossed to do, display months it is no linked to any thing
 
Good day Bobhc,


Yes, it is displaying month only, but want to generate Resource monthly production based on it. If i change the month from dropdown then i can get the actual production by resource in that month.
 
Bhasoriya,


I just checked the formula and it is working OK. Do you mean that LA is the month you are looking for? I am unable to understand this point of yours.


[Regarding your last post that answers Bob's questions] If you want to change data based on Month then you should first relate it to a cell. Only then it could change the argument of the formula and then it could work


Regards,
 
Hi Bhasoriya!


Try this also..


* Assuming.. LA sheet is only contain a Single year's data.. so that APR meand APR-13 not APR(11/15)


Code: 
=SUMPRODUCT((TEXT(LA!$B$3:$B$44,"mmm")='Resource Monthly'!$A$1)*(LA!$C$3:$C$44='Resource Monthly'!$A4))


or little bit longer but faster than above formula..


=COUNTIFS(LA!$B$4:$B$44,">="&1&'Resource Monthly'!$A$1,LA!$B$4:$B$44,"<="&EOMONTH(1&'Resource Monthly'!$A$1,0),LA!$C$4:$C$44,'Resource Monthly'!$A4)


Regards,

Deb
 
Thanks Debraj,Faseeh and all,


@Debraj, It is exactly that what i want... Both formulas are working perfectly...

Thanks a lot from depth of heart.


Thanks you all.


Regards,

Bhasoriya
 
I am a newbie in this area. I answering this so that I can know whether this is a valid answer to this problem. I done this with array formula.


COUNT(IF(((MONTH(LA!$B$18:$B$44)=MONTH(DATEVALUE("01-"&$A$1)))*(LA!$C$18:$C$44=A4)),VALUE(LA!$D$18:$D$44)))
 
Hello Asoka,


This is not getting that answer what i want. Debraj's post has given me exact result.Kindly refer it.


Thanks for you nice efforts..


Regards

Bhasoriya.
 
@ Debraj,


Could you please help to SUM this, SUM of SUMPRODUCT


http://rapidshare.com/files/3474248823/Monthly%20report.xlsx
 
Hi Bhasoriya,


Try this..


Code: 
=SUMPRODUCT((TEXT(LA!$B$3:$B$44,"mmm")='Resource Monthly'!$A$1)*(LA!$C$3:$C$44='Resource Monthly'!$A4),LA!$F$3:$F$44)


Regards,

Deb
 
You must log in or register to reply here.
Back
Top