i am trying to use this formula but it result 0 pls help me why it is not working
=SUMPRODUCT(COUNTIFS(Sheet2!B:B,"1",Sheet2!M:M,"2",Sheet2!J:J,{"3","4","5","6","7","8","9","10"},Sheet2!AF:AF,{"1"})*--(MONTH(Sheet2!I:I)=MONTH(Sheet4!D$13))*--(YEAR(Sheet2!I:I)=YEAR(Sheet4!D$13)))
You can either use "*"(multiplication) or double unary "--".
Try this
=SUMPRODUCT(COUNTIFS(Sheet2!B:B,"1",Sheet2!M:M,"2",Sheet2!J:J,{"3","4","5","6","7","8","9","10"},Sheet2!AF:AF,{"1"}),--(MONTH(Sheet2!I:I)=MONTH(Sheet4!D$13)),--(YEAR(Sheet2!I:I)=YEAR(Sheet4!D$13)))
Otherwise post your workbook
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Your COUNTIFS function returns an 8 element array (8 because {"3","4","5","6","7","8","9","10"} has 8 elements) and then you are trying to combine that with whole column arrays for month and year so that won't work.
Try doing the MONTH/YEAR part within the COUNTIF function by returning the start and end date of the month using EOMONTH, like this
=SUMPRODUCT(COUNTIFS(Sheet2!B:B,1,Sheet2!M:M,2,Sheet2!J:J,{3,4,5,6,7,8,9,10},Sheet2!AF:AF,1,Sheet2!I:I,">="&EOMONTH(Sheet4!D$13,-1)+1,Sheet2!I:I,"<"&EOMONTH(Sheet4!D$13,0)+1))
Audere est facere
thanks dear
=SUMPRODUCT(COUNTIFS(Sheet1!J:J,"2",Sheet1!K:K,"*",Sheet1!B:B,">="&EOMONTH(Sheet2!D11,-3)+1,Sheet1!B:B,"<"&EOMONTH(Sheet2!D11,0)+1)) it work perfectely, now i want add one more criteria to it, i want that its count the value only in Jan., April, July and October only mean that only at the end of quarter only
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks