|
help in formula countif OR sumproduct
Dear Sir,
i want to know the formula how to count value of B column 20, 40, 40hc & 40flt which value in C clumn is starting from zero and grader then zero (except minuse value) example a b c d 12348 20 0 almar 23456 40 2 jv 98745 40hc -2 jv 25896 40flt 10 rsl 12348 20 -1 almar 23456 40 9 cim 98745 40hc -2 rsl 25896 40flt 13 rsl 12348 20 0 rsl 23456 40 2 cim 98745 40hc -2 almar 25896 40flt 10 cim i need the result in following shap 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 TOTAL 20 40 40hc 40flt 02 03 00 03 i need the formula like countif or sumproduct also i need conditional formula in above result how many cntrs are coverd in 0-5 days and how many covered in 6-10 days and how many after 10 days like below ===========<<0-5 days====== =========<<6-10days========== ========<<more then 10days========= 20 40 40HC 40FLT 20 40 40HC 40FLT 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 thanks in advance for support and help regards Malik |
help in formula countif OR sumproduct
Hello
Sample data in A2:D13 In F2:F5 and down you have "almar" etc. In G1:J1 20, 40 etc. Enter this formula in G2 and copy it to fill the table. =SUMPRODUCT(--($B$2:$B$13=G$1),--($D$2:$D$13=$F2),--($C$2:$C$13=0)) Last question, I can not see how you want to split the data. Regards, Per "Malik Nadeem" skrev i meddelelsen ... Dear Sir, i want to know the formula how to count value of B column 20, 40, 40hc & 40flt which value in C clumn is starting from zero and grader then zero (except minuse value) example a b c d 12348 20 0 almar 23456 40 2 jv 98745 40hc -2 jv 25896 40flt 10 rsl 12348 20 -1 almar 23456 40 9 cim 98745 40hc -2 rsl 25896 40flt 13 rsl 12348 20 0 rsl 23456 40 2 cim 98745 40hc -2 almar 25896 40flt 10 cim i need the result in following shap 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 TOTAL 20 40 40hc 40flt 02 03 00 03 i need the formula like countif or sumproduct also i need conditional formula in above result how many cntrs are coverd in 0-5 days and how many covered in 6-10 days and how many after 10 days like below ===========<<0-5 days====== =========<<6-10days========== ========<<more then 10days========= 20 40 40HC 40FLT 20 40 40HC 40FLT 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 thanks in advance for support and help regards Malik |
help in formula countif OR sumproduct
Hi,
This would be a nice time to consider a pivot table, with the column D's data in the Row area, column B's data in the Column area, and column A's data in the Data area with the calculation set to Count. You must have one row of titles in your data set, which you haven't shown us. As far as the breakout of your data by 0-5, 6-10 and so on, you can't do that without some data which indicates the age of the items and you don't show any such fields in your raw data. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Malik Nadeem" wrote: Dear Sir, i want to know the formula how to count value of B column 20, 40, 40hc & 40flt which value in C clumn is starting from zero and grader then zero (except minuse value) example a b c d 12348 20 0 almar 23456 40 2 jv 98745 40hc -2 jv 25896 40flt 10 rsl 12348 20 -1 almar 23456 40 9 cim 98745 40hc -2 rsl 25896 40flt 13 rsl 12348 20 0 rsl 23456 40 2 cim 98745 40hc -2 almar 25896 40flt 10 cim i need the result in following shap 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 TOTAL 20 40 40hc 40flt 02 03 00 03 i need the formula like countif or sumproduct also i need conditional formula in above result how many cntrs are coverd in 0-5 days and how many covered in 6-10 days and how many after 10 days like below ===========<<0-5 days====== =========<<6-10days========== ========<<more then 10days========= 20 40 40HC 40FLT 20 40 40HC 40FLT 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 thanks in advance for support and help regards Malik |
help in formula countif OR sumproduct
in the last i want to split data slap wise like slap one starting from zero
and ending 5 (2nd slap starting from 6 and ending 10) slap third starting from 11 and on an on 999999999 FYI column C is the days in given formula will be working like this =SUMPRODUCT(--($B$2:$B$13=G$1),--($D$2:$D$13=$F2),--($C$2:$C$13=0)) in the last array i.e. ($C$2:$C$13=0)) it should be like this ($C$2:$C$13{{ equal to zero and less then +6 or between -1 to +6)) in a 2nd slap ($C$2:$C$13{{ equal to +6 and less then +12 or between +5 to +12)) in a 3rd slap ($C$2:$C$13=12)) hope clear "Per Jessen" wrote: Hello Sample data in A2:D13 In F2:F5 and down you have "almar" etc. In G1:J1 20, 40 etc. Enter this formula in G2 and copy it to fill the table. =SUMPRODUCT(--($B$2:$B$13=G$1),--($D$2:$D$13=$F2),--($C$2:$C$13=0)) Last question, I can not see how you want to split the data. Regards, Per "Malik Nadeem" skrev i meddelelsen ... Dear Sir, i want to know the formula how to count value of B column 20, 40, 40hc & 40flt which value in C clumn is starting from zero and grader then zero (except minuse value) example a b c d 12348 20 0 almar 23456 40 2 jv 98745 40hc -2 jv 25896 40flt 10 rsl 12348 20 -1 almar 23456 40 9 cim 98745 40hc -2 rsl 25896 40flt 13 rsl 12348 20 0 rsl 23456 40 2 cim 98745 40hc -2 almar 25896 40flt 10 cim i need the result in following shap 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 TOTAL 20 40 40hc 40flt 02 03 00 03 i need the formula like countif or sumproduct also i need conditional formula in above result how many cntrs are coverd in 0-5 days and how many covered in 6-10 days and how many after 10 days like below ===========<<0-5 days====== =========<<6-10days========== ========<<more then 10days========= 20 40 40HC 40FLT 20 40 40HC 40FLT 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 thanks in advance for support and help regards Malik |
help in formula countif OR sumproduct
No problem, just add an extra criterium for column C:
=SUMPRODUCT(--($B$2:$B$13=G$1),--($D$2:$D$13=$F2),--($C$2:$C$13=0),--($C$2:$C$13<6)) =SUMPRODUCT(--($B$2:$B$13=M$1),--($D$2:$D$13=$F2),--($C$2:$C$13=6),--($C$2:$C$13<12)) =SUMPRODUCT(--($B$2:$B$13=S$1),--($D$2:$D$13=$F2),--($C$2:$C$13=12)) Regards, Per "Malik Nadeem" skrev i meddelelsen ... in the last i want to split data slap wise like slap one starting from zero and ending 5 (2nd slap starting from 6 and ending 10) slap third starting from 11 and on an on 999999999 FYI column C is the days in given formula will be working like this =SUMPRODUCT(--($B$2:$B$13=G$1),--($D$2:$D$13=$F2),--($C$2:$C$13=0)) in the last array i.e. ($C$2:$C$13=0)) it should be like this ($C$2:$C$13{{ equal to zero and less then +6 or between -1 to +6)) in a 2nd slap ($C$2:$C$13{{ equal to +6 and less then +12 or between +5 to +12)) in a 3rd slap ($C$2:$C$13=12)) hope clear "Per Jessen" wrote: Hello Sample data in A2:D13 In F2:F5 and down you have "almar" etc. In G1:J1 20, 40 etc. Enter this formula in G2 and copy it to fill the table. =SUMPRODUCT(--($B$2:$B$13=G$1),--($D$2:$D$13=$F2),--($C$2:$C$13=0)) Last question, I can not see how you want to split the data. Regards, Per "Malik Nadeem" skrev i meddelelsen ... Dear Sir, i want to know the formula how to count value of B column 20, 40, 40hc & 40flt which value in C clumn is starting from zero and grader then zero (except minuse value) example a b c d 12348 20 0 almar 23456 40 2 jv 98745 40hc -2 jv 25896 40flt 10 rsl 12348 20 -1 almar 23456 40 9 cim 98745 40hc -2 rsl 25896 40flt 13 rsl 12348 20 0 rsl 23456 40 2 cim 98745 40hc -2 almar 25896 40flt 10 cim i need the result in following shap 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 TOTAL 20 40 40hc 40flt 02 03 00 03 i need the formula like countif or sumproduct also i need conditional formula in above result how many cntrs are coverd in 0-5 days and how many covered in 6-10 days and how many after 10 days like below ===========<<0-5 days====== =========<<6-10days========== ========<<more then 10days========= 20 40 40HC 40FLT 20 40 40HC 40FLT 20 40 40HC 40FLT ALMAR 01 00 00 00 JV 00 01 00 00 CIM 01 02 00 01 RSL 00 00 00 02 thanks in advance for support and help regards Malik |
| All times are GMT +1. The time now is 07:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com