|
Jululian Excel
Hi Dear All,
i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Hi George
The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Good Morning Dear Sir,
i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
=SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370)
Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
You are genius
Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Welcome and thanks for the feedback..
If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Dear Sir,
i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
I tried this...(Array entered) and gives the correct result.
If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then return the total of fleet If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir, i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Sir,
The record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 waht i need is only the total fleet for b1,b2,b3 and second why when i enter the formula it request to upadte the link and i know there is no links regards "Jacob Skaria" wrote: I tried this...(Array entered) and gives the correct result. If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then return the total of fleet If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir, i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Try the same formula in a new workbook with 2 sheets named as Stock and
Statistics..and with some dummy data.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Sir, The record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 waht i need is only the total fleet for b1,b2,b3 and second why when i enter the formula it request to upadte the link and i know there is no links regards "Jacob Skaria" wrote: I tried this...(Array entered) and gives the correct result. If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then return the total of fleet If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir, i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
No Sir did work the formula
"Jacob Skaria" wrote: Try the same formula in a new workbook with 2 sheets named as Stock and Statistics..and with some dummy data.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Sir, The record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 waht i need is only the total fleet for b1,b2,b3 and second why when i enter the formula it request to upadte the link and i know there is no links regards "Jacob Skaria" wrote: I tried this...(Array entered) and gives the correct result. If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then return the total of fleet If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir, i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Dear Sir
=IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2,'Daily Report'!H9=Statistics!B3),SUMPRODUCT((Statistics!B 5:BW5="Fleet")*Statistics!B6:BW370," ")) i need the result from Fleet column which in column c,or e,g and so on please help "Jacob Skaria" wrote: Try the same formula in a new workbook with 2 sheets named as Stock and Statistics..and with some dummy data.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Sir, The record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 waht i need is only the total fleet for b1,b2,b3 and second why when i enter the formula it request to upadte the link and i know there is no links regards "Jacob Skaria" wrote: I tried this...(Array entered) and gives the correct result. If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then return the total of fleet If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir, i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Syntax error .Try the below
=IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2, 'Daily Report'!H9=Statistics!B3), SUMPRODUCT((Statistics!B5:BW5="Fleet")*Statistics! B6:BW370),"") If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir =IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2,'Daily Report'!H9=Statistics!B3),SUMPRODUCT((Statistics!B 5:BW5="Fleet")*Statistics!B6:BW370," ")) i need the result from Fleet column which in column c,or e,g and so on please help "Jacob Skaria" wrote: Try the same formula in a new workbook with 2 sheets named as Stock and Statistics..and with some dummy data.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Sir, The record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 waht i need is only the total fleet for b1,b2,b3 and second why when i enter the formula it request to upadte the link and i know there is no links regards "Jacob Skaria" wrote: I tried this...(Array entered) and gives the correct result. If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then return the total of fleet If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir, i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
I am very sorry that cause you headack on this issue
the result form the formula is 12 and should read 1 because need is if IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2, 'Daily Report'!H9=Statistics!B3) is ture then i need the result for these only (FLEET) and not the all table result please help "Jacob Skaria" wrote: Syntax error .Try the below =IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2, 'Daily Report'!H9=Statistics!B3), SUMPRODUCT((Statistics!B5:BW5="Fleet")*Statistics! B6:BW370),"") If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir =IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2,'Daily Report'!H9=Statistics!B3),SUMPRODUCT((Statistics!B 5:BW5="Fleet")*Statistics!B6:BW370," ")) i need the result from Fleet column which in column c,or e,g and so on please help "Jacob Skaria" wrote: Try the same formula in a new workbook with 2 sheets named as Stock and Statistics..and with some dummy data.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Sir, The record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 waht i need is only the total fleet for b1,b2,b3 and second why when i enter the formula it request to upadte the link and i know there is no links regards "Jacob Skaria" wrote: I tried this...(Array entered) and gives the correct result. If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then return the total of fleet If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir, i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Interesting. Try the below test in a new sheet.
Col A Col B Col C other fleet other 1 2 3 4 5 6 7 8 9 10 11 12 =SUMPRODUCT((A1:C1="Fleet")*A2:C5) 'That is the same thing mentioned in the other formula.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: I am very sorry that cause you headack on this issue the result form the formula is 12 and should read 1 because need is if IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2, 'Daily Report'!H9=Statistics!B3) is ture then i need the result for these only (FLEET) and not the all table result please help "Jacob Skaria" wrote: Syntax error .Try the below =IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2, 'Daily Report'!H9=Statistics!B3), SUMPRODUCT((Statistics!B5:BW5="Fleet")*Statistics! B6:BW370),"") If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir =IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2,'Daily Report'!H9=Statistics!B3),SUMPRODUCT((Statistics!B 5:BW5="Fleet")*Statistics!B6:BW370," ")) i need the result from Fleet column which in column c,or e,g and so on please help "Jacob Skaria" wrote: Try the same formula in a new workbook with 2 sheets named as Stock and Statistics..and with some dummy data.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Sir, The record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 waht i need is only the total fleet for b1,b2,b3 and second why when i enter the formula it request to upadte the link and i know there is no links regards "Jacob Skaria" wrote: I tried this...(Array entered) and gives the correct result. If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then return the total of fleet If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir, i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
Jululian Excel
Good morning Sir,
A B C D E F Orange Orange Banana Banana Tomato Tomato Other Fleet Other Fleet Other Fleet 1 2 33 1 8 1 4 5 6 5 4 9 7 8 9 8 35 5 10 11 12 9 66 4 the formula is to find in the table the sum of Banana fleet please help Regards "Jacob Skaria" wrote: Interesting. Try the below test in a new sheet. Col A Col B Col C other fleet other 1 2 3 4 5 6 7 8 9 10 11 12 =SUMPRODUCT((A1:C1="Fleet")*A2:C5) 'That is the same thing mentioned in the other formula.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: I am very sorry that cause you headack on this issue the result form the formula is 12 and should read 1 because need is if IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2, 'Daily Report'!H9=Statistics!B3) is ture then i need the result for these only (FLEET) and not the all table result please help "Jacob Skaria" wrote: Syntax error .Try the below =IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2, 'Daily Report'!H9=Statistics!B3), SUMPRODUCT((Statistics!B5:BW5="Fleet")*Statistics! B6:BW370),"") If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir =IF(AND('Daily Report'!F9=Statistics!B1,'Daily Report'!G9=Statistics!B2,'Daily Report'!H9=Statistics!B3),SUMPRODUCT((Statistics!B 5:BW5="Fleet")*Statistics!B6:BW370," ")) i need the result from Fleet column which in column c,or e,g and so on please help "Jacob Skaria" wrote: Try the same formula in a new workbook with 2 sheets named as Stock and Statistics..and with some dummy data.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Sir, The record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 waht i need is only the total fleet for b1,b2,b3 and second why when i enter the formula it request to upadte the link and i know there is no links regards "Jacob Skaria" wrote: I tried this...(Array entered) and gives the correct result. If a record exists in Sheet 'Stock' matching the criterias of B1,B2,B3 then return the total of fleet If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Dear Sir, i amend the formula to read =IF(ISNA(MATCH(1,(Stock!$A$3:$A$35=Statistics!B1)* (Stock!$B$3:$B$35=Statistics!B2)* (Stock!$C$3:$C$35=Statistics!B3),0)),"",SUMPRODUCT ((Statistics!B5:BW5="Fleet")*Statistics!B6:BW370)) and the result it gave me the all sales value and not my condition please help "Jacob Skaria" wrote: Welcome and thanks for the feedback.. If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: You are genius Many thanks teacher "Jacob Skaria" wrote: =SUMPRODUCT((Statistics!B5:BW5="fleet")*Statistics !B6:BW370) Didnt you notice the second formula I suggested =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Good Morning Dear Sir, i wrote the following =SUMPRODUCT(("Statistics!B5:BW5),*Statistics!B6:BW 370")) the result #VALUE! please advice Regards "Jacob Skaria" wrote: Hi George The below formula will sum the fleet entries for the month specified in cell A2. =SUMPRODUCT((TEXT(A2:A100,"mmyyyy")=TEXT(A2,"mmyyy y"))*(B1:G1="Fleet"),B2:G100) You can take out the first condition if you want a full sum of fleets =SUMPRODUCT((B1:G1="Fleet")*B2:G100) If this post helps click Yes --------------- Jacob Skaria "George A. Jululian" wrote: Hi Dear All, i have tabe like below B C D E F G Date Retail Fleet Retail Fleet Retail Fleet 01-Jan-09 02-Jan-09 03-Jan-09 i need formula to sum only the fleet sales please help regards |
| All times are GMT +1. The time now is 07:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com