| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Jululian Excel | Excel Discussion (Misc queries) | |||
| Jululian Excel | Excel Discussion (Misc queries) | |||
| Jululian & Excel | Excel Discussion (Misc queries) | |||
| Excel Jululian | Excel Discussion (Misc queries) | |||
| Excel Jululian | Excel Discussion (Misc queries) | |||
![George A. Jululian[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
