User Profile
Rajesh_Sinha
Iron Contributor
Joined 8 years ago
User Widgets
Recent Discussions
Re: Data Aggregation base on dates
ImalkaJ For the time being try solution I've used in the attached file & let me try something better. ===================================================== This is the best way I found & it's working. How it works: For better data management & visualization I've altered the format of the OUTPUT, you can also realize that using formula on the format used by you is hectic as well precarious. An array (CSE) Formula in cell F26: {=IFERROR(INDEX($B$26:$B$42, MATCH(SMALL(IF(COUNTIF($F$25:F25, $B$26:$B$42)=0, COUNTIF($B$26:$B$42, "<"&$B$26:$B$42), ""), 1), COUNTIF($B$26:$B$42, "<"&$B$26:$B$42), 0)),"")} Use this formula in G26: =SUMPRODUCT(($A$26:$A$42=G$25)*($B$26:$B$42=$F26)*($C$26:$C$42)) Cell G33 has SUM formula. Adjust cell references in the formula as needed. Finish an array (CSE) formula with Ctrl+Shift+Enter.Re: AUTO PRINT
After I've examined the attached file the Macros you are using are copying cell value from column F and pasting in F12 then printing entire sheet rather than the SLIP,, I guess the SLIP is in the right most part having SKU and others,,, but is ONLY ONE,,, So my questions is,,, 1. How far I'm correct. 2. If I'm not wrong then actually you want to print variable number of copy of the SLIP! If yes please confirm then I'll show you how to do this.Re: Please Help With this Excel Problem!
lanem1010 This solves the issue: Formula in cell H66: =INDEX($B$66:$E$77,MATCH($H$64,$A$66:$A$77,1),MATCH($H$65,$B$65:$E$65,1)) Array (CSE) formula in J68: {=AVERAGE(IF(A66:A77=12,B66:E77))} Array (CSE) formula in J69: {=MAX(IF($B$65:$E$65=$H$69,$B$66:$E$77))} Formula in H74: =SUMPRODUCT(($B$65:$E$65=$H$71)*($A$66:$A$77>=$H$72)*($A$66:$A$77<=$H$73)*($B$66:$E$77)) N.B. Finish both array (CSE) formula with Ctrl+Shift+Enter. Adjust cell references as needed.Re: SUM totals using VLOOKUP?
MarcusBam You may try this one: :Caveat: For better understanding & visualization I've taken smaller data set you need to extend as required. Formula in cell U2: =SUMPRODUCT(($W$2:$W$21>=$S2)*($W$2:$W$21<=$T2)*($X$2:$X$21)) N.B. Adjust cell references in the formula as needed.Re: monthly average each year
osamashokeir This solves the issue: How it works: Write first Date of each month for respected years in Column E & apply cell format mmm-yyyy. Enter this formula in cell F39 & fill down. =IFERROR(AVERAGEIFS($C$39:$C$58,$B$39:$B$58,">="&$E39,$B$39:$B$58,"<="&EOMONTH($E39,0)),0) N.B. Formula returns ZERO for moths, are not available in Column B. You may use this formula also: =IFERROR(SUMPRODUCT((MONTH($B$39:$B$58)=MONTH(E39)*(YEAR($B$39:$B$58)=YEAR(E39)))*($C$39:$C$58))/(SUMPRODUCT((MONTH($B$39:$B$58)=MONTH(E39)*(YEAR($B$39:$B$58)=YEAR(E39)))*1)),0) Accept this as Best response as well Like, if my post solves the issue.
