ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   sumproduct replace with sumif (https://www.excelbanter.com/excel-discussion-misc-queries/268020-sumproduct-replace-sumif.html)

avk

sumproduct replace with sumif
 
Hi friends,
In excel 2003 i m using below sumproduct formula for calculate duration and its work. But i want to using sumif instead of sumproduct.
=TEXT(INT(SUMPRODUCT((FSR_NGP!AE$2:AE$65001=$A5)*( FSR_NGP!S$2:S$65001=$E$4),FSR_NGP!U$2:U$65001)), "00 ")&TEXT(SUMPRODUCT((FSR_NGP!AE$2:AE$65001=$A5)*(FS R_NGP!S$2:S$65001=$E$4),FSR_NGP!U$2:U$65001),"hh :mm")

AE & A5 = employee code
S & E4 = month & year
U = duration (format with dd hh:mm)
Above formula calculat perfectly but with sumproduct is work slow & i want to used sumif formula.

In FSR_NGP sheet in column AH i have join month,year & employee code.
But i have facing problem when replacing sumif function.


All times are GMT +1. The time now is 11:20 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com