Finding common data in multiple columns and rows in Excel

I'm trying to determine the number of times that certain data appears in two
columns, on the same row. For example:
Below are two columns. I want to know how many times that column G has
"apple" and column K has "pie". I'm only interested in knowing how many rows
contain both "apple" and "pie".
Column G Column K
apple pie
apple cider
orange juice
banana pudding
apple pie
banana bread
kiwi pie
apple pie
orange juice
kiwi juice
kiwi pie
orange danish
apple cider
orange juice
apple pie
orange juice
kiwi juice

Using the correct function(s), I should be able to get the answer; 4. But I
don't know what function(s) to use.

=SUMPRODUCT(--(G2:G100="apple"),--(K2:K100="pie"))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"sparham" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to determine the number of times that certain data appears in
> two
> columns, on the same row. For example:
> Below are two columns. I want to know how many times that column G has
> "apple" and column K has "pie". I'm only interested in knowing how many
> rows
> contain both "apple" and "pie".
> Column G Column K
> apple pie
> apple cider
> orange juice
> banana pudding
> apple pie
> banana bread
> kiwi pie
> apple pie
> orange juice
> kiwi juice
> kiwi pie
> orange danish
> apple cider
> orange juice
> apple pie
> orange juice
> kiwi juice
>
> Using the correct function(s), I should be able to get the answer; 4. But
> I
> don't know what function(s) to use.

One way

Assume source data is in Sheet1, cols G and K, rows 2 to 20

In Sheet2
-------------

Assuming you have in A2: apple, in B2: pie, with other similar paired inputs
in A3:B3, A4:B4, etc

Put in C2:

=SUMPRODUCT((TRIM(Sheet1!$G$2:$G$20)=TRIM(A2))*(TRIM(Sheet1!$K$2:$K$20)=TRIM
(B2)))

Copy C2 down

Adapt the ranges to suit, but note that you can't use entire col refs (A:A,
B:B, etc) within SUMPRODUCT

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"sparham" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to determine the number of times that certain data appears in
two
> columns, on the same row. For example:
> Below are two columns. I want to know how many times that column G has
> "apple" and column K has "pie". I'm only interested in knowing how many
rows
> contain both "apple" and "pie".
> Column G Column K
> apple pie
> apple cider
> orange juice
> banana pudding
> apple pie
> banana bread
> kiwi pie
> apple pie
> orange juice
> kiwi juice
> kiwi pie
> orange danish
> apple cider
> orange juice
> apple pie
> orange juice
> kiwi juice
>
> Using the correct function(s), I should be able to get the answer; 4. But
I
> don't know what function(s) to use.

Thank you, Peo Sjoblom! That was what I needed to know in order to get the
totals that I needed.

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(G2:G100="apple"),--(K2:K100="pie"))
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please, for everyone's
> benefit keep the discussion in the newsgroup/forum)
>
>
>
> "sparham" <[email protected]> wrote in message
> news:[email protected]...
> > I'm trying to determine the number of times that certain data appears in
> > two
> > columns, on the same row. For example:
> > Below are two columns. I want to know how many times that column G has
> > "apple" and column K has "pie". I'm only interested in knowing how many
> > rows
> > contain both "apple" and "pie".
> > Column G Column K
> > apple pie
> > apple cider
> > orange juice
> > banana pudding
> > apple pie
> > banana bread
> > kiwi pie
> > apple pie
> > orange juice
> > kiwi juice
> > kiwi pie
> > orange danish
> > apple cider
> > orange juice
> > apple pie
> > orange juice
> > kiwi juice
> >
> > Using the correct function(s), I should be able to get the answer; 4. But
> > I
> > don't know what function(s) to use.
>
>
>