Hello everyone,
The formula works perfectly: =COUNT(MATCH(D1:D6,A1:A26,0)), with array applied Ctrl+Shift+Enter.
But the formula =COUNT(INDEX(B1:B26,MATCH(D1:D6,A1:A26,0))) does not work. Is it because the INDEX function does not support array?
I got the example here. Thanks for helping me.
Try this ...
=COUNT(INDEX(MATCH(D1:D6,A1:A26,0),0))
I need to count the column B please.
Try this:
=SUMPRODUCT((A1:A26={"A","AA","G","M","O","Z"})*B1:B26)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi, I really need to specify them as range which contains thousands of cells in the range. So I cannot specify them in advance.
Is there a formula that can specify the range?
If this is not possible, maybe a UDF needs to be created
I don't know. I feel a normal Excel formula should do.
I got the formula working now:
=SUM(IF(ISERROR(MATCH(A1:A26,D1:D6,0)),"",B1:B26)), with array applied Ctrl+Shift+Enter.
Hello Bill,
You can use SUMIF with just ENTER
=SUMPRODUCT(SUMIF(A:A,D1:D6,B:B))
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
Sorry, try:Originally Posted by billj
=SUMPRODUCT(B1:B26*COUNTIF(D1:D6,A1:A26))
If the values in B can be other than 1, and it is a COUNT rather than a SUM you can also use this array:
=COUNT(IF($B$1:$B$26>0,IF(ISERROR(MATCH($A$1:$A$26,$D$1:$D$6,0)),"",$B$1:$B$26)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks