PDA

View Full Version : Formula help for counting indexed cells?

dabenxiang12
05-18-2008, 06:48 AM
two tables，tabel a：
index name product
1 x x
2 u u
table b：
index property map content
1 1 1 2
2 2 1 3
3 1 2 2
question1：records in table a is connected with records in table b through the segement map(map in table b directs table a's index),for the example,the first record in table a connects withs the first two records in table b,the second record in table connects with the third record in table b,now i want to count the number of records in table a on condition that the property of any record in table b which is connected with the records in table a is 1,then the record in table a is counted.through this algorithm，the result of the question is 2,how to write the excel function .
question 2：from the first record to the last record in table a,i want to sum up the content of all the records in table b connected with the record in table a ,the result of the question is :the first record in table a:3+2=5
the second record in table b:2
how to write the excel formula,
hurry ,please help me!

Simon Lloyd
05-18-2008, 09:02 AM
dabenxiang12 please look at this link (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_posthelp_faq_item") with regards to your title and look at this link (http://www.vbaexpress.com/forum/faq.php?faq=psting_faq_item#faq_hom_faq_item) with regards your subject matter as we cannot help directly with homework or assignment questions!

Simon Lloyd
05-18-2008, 09:17 AM
Also when you cross post you should supply the link however in this case it probably doesn't matter as we don't (well i don't) speak Chinese!
cross post (http://club.excelhome.net/dispbbs.asp?boardid=3&id=322915&star=1&page=1)

dabenxiang12
05-18-2008, 03:44 PM
what you said is right, but this is not homework or assignment ,in fact ,table a and b are complicated tables ,I just want to simplify the expression of the matter that should be resolved .
I also tried nearly all the excel functions to resolve the question ,but maybe I am not familiar with excel ,I still can't find the solution ,so I came here for help.
by the way ,i am not a student .

if somebody carefully reads the question ,it will find this is a challenging question,because the first row in table a connects the first two rows in table b, the scecond row in table a connects the third row in table b. but nearly all the excel functions process retrieve like the following way: table a 1st row->table b 1st row
table a 2nd row->table b 2nd row(table a 2nd row should connects table b 3rd row).
I know how to resolve the question through programs ,but i don't know how to write the excel functions

Simon Lloyd
05-18-2008, 04:34 PM
You probably need to use SumProduct something like:
=IF(SUMPRODUCT(--(A1:B1000=A1),--(Sheet2!A1:B1000=1))=0,"",SUMPRODUCT(--(A1:B1000=A1),--(Sheet2!A1:B1000=1)))

xld
05-19-2008, 12:29 AM
The only way that I can get the results that OP gets is with

=SUMIF(\$C\$6:\$C\$8,A1,\$D\$6:\$D\$8)

but it is so poorly explained I wouldn't get that from the description.

Simon Lloyd
05-19-2008, 01:10 AM
I know Bob i was having great trouble deciphering what was needed, and i don't really have the formula skills, the sumproduct i was lamely tying to use was to try and count rows that 1 appeared in as per part of the Op's question, however it seems as though he's trying to explain a kind of cascade calculation but it's inconsistent as 1st row is dependant on two rows in another sheet yet 2nd row is only dependant on 1 row, he says its a complicated table and i'm sure it is if it has no real structure or pattern to it!

xld
05-19-2008, 01:27 AM
Yes it is difficult to see why row 2 is added to the 1s and row 3 is added to the 2s.