Hi
I have three columns that contain numbers ranging from 1 to 10.
I wish to count the number of times say 2, 4 and 6 occur in each of the three columns and come up with a total
i.e. count # of times 2, 4 and 6 occur in all three columns
I tried SUMPRODUCT but keep getting zero
=SUMPRODUCT (--($A$2:$A$40=2)--($B$2:$B$40=2)--($C$2:$C$40=2)) and repeat for 4 and 6
Any suggestions ?
Bob M
I think you forgot about *
=SUMPRODUCT (--($A$2:$A$40=2)*--($B$2:$B$40=2)*--($C$2:$C$40=2)) or
=SUMPRODUCT (--($A$2:$A$40=2),--($B$2:$B$40=2),--($C$2:$C$40=2)) and so on... (no example - not tested)
Last edited by sandy666; 12-04-2017 at 07:43 PM.
Hi,
One way
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hello Richard
Yes - that will work - I guess I was hoping that SUMPRODUCT would be a little shorter in length
=sumproduct(COUNTIF(A2:C40, {2,3,4})
Entia non sunt multiplicanda sine necessitate
Hi shg
brilliant - that's what I was seeking.................
Now, one last tweek
What if the 3 columns were not A, B & C but A, E and G?
Bob M
Try:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Try:
Formula:Please Login or Register to view this content.
Try =SUMPRODUCT(COUNTIFS(OFFSET(A2:A40,,{0,4,6}),{2;3;4}))
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks