Hi all,
I am working on a formula where I need to differentiate between type and colour please see example
Column A Column D
1Animal Colour
2Dog Black
3Cat Brown
4Cat White
5Dog White
So what I want is all White Dogs, All White Cats, All Black Dogs, All Brown Cats etc etc. I think DCOUNTA is perhaps the best for this but I need the criteria to be two conditions for each.
How do I combine two conditions over two non-adjacent columns?
Try something like this....
With your source data in Columns A and D,
Where
Col_A contains Animals
Col_D contains Color
Then...
Y1: Dog
Z1: Black
AA1: =SUMPRODUCT(($A$1:$A$100=J1)*($D$1:$D$100=K1))
Set other search values in Columns Y and Z
then copy cell AA1 down as far as you need
ALTERNATIVELY....
You could use a Pivot Table to do all of the heavy lifting for you.
The end result would like something like this:
animal color Total
Cat Brown 1
White 1
Dog Black 1
White 1
Does that help?
Hi,
I should have mentioned that the text I am searching for is within paragraphs of text on each row so
AA1: =SUMPRODUCT(($A$1:$A$100=J1)*($D$1:$D$100=K1))
will not work. I'll try messing about with a pivot table as I hadn't thought of that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks