countif formula
Hi, i have created a sheet that uses countif for data of other spreadsheets.
is there a way to retrieve the data without opening all the the source data files each time? cheers |
countif formula
Use SUMPRODUCT instead
=SUMPRODUCT(--(book_reference=value)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi, i have created a sheet that uses countif for data of other spreadsheets. is there a way to retrieve the data without opening all the the source data files each time? cheers |
countif formula
thanks for your reply but i cannot make it work.
The formula with the countif is the following: =COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p") where MDR is the other file, h is the column where i look for the data (the letter P in this particular occasion) if I just substitute COUNTIF with SUMPRODUCT it doesn't work.... "Bob Phillips" wrote: Use SUMPRODUCT instead =SUMPRODUCT(--(book_reference=value)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi, i have created a sheet that uses countif for data of other spreadsheets. is there a way to retrieve the data without opening all the the source data files each time? cheers |
countif formula
=SUMPRODUCT(--(A1:A10="g"))
it works fine if the fomula stays in the same sheet as the data but if the range A1:A10 is in another spread sheet i cannot find the right sintax for it. '[fileMDR.xls]Drwgs'!$H:$H this is how the fomula with the file name would be like if I used countif so i have tried to copy it in the sumproduct but with no luck "Bob Phillips" wrote: Use SUMPRODUCT instead =SUMPRODUCT(--(book_reference=value)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi, i have created a sheet that uses countif for data of other spreadsheets. is there a way to retrieve the data without opening all the the source data files each time? cheers |
countif formula
=SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p"))
Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... thanks for your reply but i cannot make it work. The formula with the countif is the following: =COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p") where MDR is the other file, h is the column where i look for the data (the letter P in this particular occasion) if I just substitute COUNTIF with SUMPRODUCT it doesn't work.... "Bob Phillips" wrote: Use SUMPRODUCT instead =SUMPRODUCT(--(book_reference=value)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi, i have created a sheet that uses countif for data of other spreadsheets. is there a way to retrieve the data without opening all the the source data files each time? cheers |
countif formula
Hi Bob.
sorry to bother you but it still doesn't work. I tried that sintax but it's not working. By the way i have noticed that the fomrula would not work for the whole column H.. I have tried to put an absolute link to the file like =SUMPRODUCT(--('[N:\FPSO\12. TechDoc\00 KUI MDR\[MDR.xls]Drwgs'!$H1:$H1000="s")) but it says that the formula contains one or more invalid references and to verify that the formula contains a valid path, workbook, range name and cell reference. is thre somthing i'm missing? thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p")) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... thanks for your reply but i cannot make it work. The formula with the countif is the following: =COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p") where MDR is the other file, h is the column where i look for the data (the letter P in this particular occasion) if I just substitute COUNTIF with SUMPRODUCT it doesn't work.... "Bob Phillips" wrote: Use SUMPRODUCT instead =SUMPRODUCT(--(book_reference=value)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi, i have created a sheet that uses countif for data of other spreadsheets. is there a way to retrieve the data without opening all the the source data files each time? cheers |
countif formula
don't know how but i managed to make it work..... i think it was a matter of
spaces and dots in the file name (not my choice..). anyway, thanks for your time and help. I have one more question pls. it just about curiosity: the sign -- what is needed for exactly? cheers "massi" wrote: Hi Bob. sorry to bother you but it still doesn't work. I tried that sintax but it's not working. By the way i have noticed that the fomrula would not work for the whole column H.. I have tried to put an absolute link to the file like =SUMPRODUCT(--('[N:\FPSO\12. TechDoc\00 KUI MDR\[MDR.xls]Drwgs'!$H1:$H1000="s")) but it says that the formula contains one or more invalid references and to verify that the formula contains a valid path, workbook, range name and cell reference. is thre somthing i'm missing? thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p")) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... thanks for your reply but i cannot make it work. The formula with the countif is the following: =COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p") where MDR is the other file, h is the column where i look for the data (the letter P in this particular occasion) if I just substitute COUNTIF with SUMPRODUCT it doesn't work.... "Bob Phillips" wrote: Use SUMPRODUCT instead =SUMPRODUCT(--(book_reference=value)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi, i have created a sheet that uses countif for data of other spreadsheets. is there a way to retrieve the data without opening all the the source data files each time? cheers |
countif formula
the most asked question in the NGs <vbg
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... don't know how but i managed to make it work..... i think it was a matter of spaces and dots in the file name (not my choice..). anyway, thanks for your time and help. I have one more question pls. it just about curiosity: the sign -- what is needed for exactly? cheers "massi" wrote: Hi Bob. sorry to bother you but it still doesn't work. I tried that sintax but it's not working. By the way i have noticed that the fomrula would not work for the whole column H.. I have tried to put an absolute link to the file like =SUMPRODUCT(--('[N:\FPSO\12. TechDoc\00 KUI MDR\[MDR.xls]Drwgs'!$H1:$H1000="s")) but it says that the formula contains one or more invalid references and to verify that the formula contains a valid path, workbook, range name and cell reference. is thre somthing i'm missing? thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p")) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... thanks for your reply but i cannot make it work. The formula with the countif is the following: =COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p") where MDR is the other file, h is the column where i look for the data (the letter P in this particular occasion) if I just substitute COUNTIF with SUMPRODUCT it doesn't work.... "Bob Phillips" wrote: Use SUMPRODUCT instead =SUMPRODUCT(--(book_reference=value)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi, i have created a sheet that uses countif for data of other spreadsheets. is there a way to retrieve the data without opening all the the source data files each time? cheers |
countif formula
thanks a lot.
BR Massi "Bob Phillips" wrote: the most asked question in the NGs <vbg See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... don't know how but i managed to make it work..... i think it was a matter of spaces and dots in the file name (not my choice..). anyway, thanks for your time and help. I have one more question pls. it just about curiosity: the sign -- what is needed for exactly? cheers "massi" wrote: Hi Bob. sorry to bother you but it still doesn't work. I tried that sintax but it's not working. By the way i have noticed that the fomrula would not work for the whole column H.. I have tried to put an absolute link to the file like =SUMPRODUCT(--('[N:\FPSO\12. TechDoc\00 KUI MDR\[MDR.xls]Drwgs'!$H1:$H1000="s")) but it says that the formula contains one or more invalid references and to verify that the formula contains a valid path, workbook, range name and cell reference. is thre somthing i'm missing? thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--('[MDR.xls]Drwgs'!$H1:$H1000="p")) Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... thanks for your reply but i cannot make it work. The formula with the countif is the following: =COUNTIF('[MDR.xls]Drwgs'!$H:$H,"p") where MDR is the other file, h is the column where i look for the data (the letter P in this particular occasion) if I just substitute COUNTIF with SUMPRODUCT it doesn't work.... "Bob Phillips" wrote: Use SUMPRODUCT instead =SUMPRODUCT(--(book_reference=value)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "massi" wrote in message ... Hi, i have created a sheet that uses countif for data of other spreadsheets. is there a way to retrieve the data without opening all the the source data files each time? cheers |
All times are GMT +1. The time now is 10:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com