Sum Product (Comparing Multiple Columns and Counting Sum

Hello,

I think I need to use a sumproduct, but not sure. Please see attached spreadsheet.

Here's what I'm trying to find:

Compare Column F to Column A
Column G should be sum the # of times the value in Column A is used with Column C

Desired Result in Column G, where formula should go.

Thanks in advance.

I believe you just need this

=COUNTIF(A:A,F2)

Hi Cutter,

I gave a bad example..let me try again..please see new attachment.

Here's what I'm trying to do

Results are in Column F & G which I'm trying to solve formula for

Column G, sum total # of unique domains, in column c, that are associated with column a, after looking at column e
Column F, count total # of pages, after looking at column e, that are associate with column a, which i belive you're formula

COUNTIF(A:A,E2) which would go in to f2 would work.


Its column g, that I'm trying to solve..

Desired result is in columns f & g

Thanks for your help.

Hi Fortis,

I will need to use cell references, as i have a very large data set, and also looking for exact matches not *, thus can't type in manually values in each cell. But appreciate you're help.

Thanks Cutter. It worked perfectly. Feel free to pm me to paypal you.

Perhaps you could explain in plain english..how it works..

1. IF(A$2:A$10=E2,MATCH(C$2:C$10,C$2:C$10,0)),MATCH(C$2:C$10,C$2:C$10,0))>0) ....if(a2:a10=e2, then match(c2:c10) ((looks and finds position # of the value), if the value is not then MATCH(C$2:C$10,C$2:C$10,0))>0, basically add a 1 for the value
2. Frequency....

?

Select cell G3 and, in the formula bar, highlight this portion of the formula: IF(A$2:A$10=E3,MATCH(C$2:C$10,C$2:C$10,0))
Hit F9 key and you'll see this result: {FALSE;FALSE;3;4;5;6;5;FALSE;FALSE}
This serves as the data array for the FREQUENCY() function.
Click Esc key and highlight this portion (after what was just highlighted): MATCH(C$2:C$10,C$2:C$10,0)
Again, hit the F9 key and you'll see this result: {1;2;3;4;5;6;5;8;9)
This is the bins array for the FREQUENCY() function.
Click the Esc key again and now highlight the FREQUENCY portion: FREQUENCY(IF(A$2:A$10=E3,MATCH(C$2:C$10,C$2:C$10,0)),MATCH(C$2:C$10,C$2:C$10,0))
Again hit the F9 key and you'll see: {0;0;1;1;2;1;0;0;0;0}
The FREQUENCY() function has looked at the results of the 2 MATCH() functions and said that there is 1 x 3, 1 x 4, 2 x 5 and 1 x 6 (and none of the others)
Hit Esc and now highlight everything between the brackets of the SUM() function
The N() function has converted TRUE/FALSE values to 1/0 for the statement >0 so you get: {0;0;1;1;1;1;0;0;0;0} instead of {FALSE;FALSE;TRUE;.........}
And finally the SUM() function just adds up those 1's and 0's and arrives at 4

Here are links to read for the FREQUENCY() function:
http://www.excelfunctions.net/Excel-...-Function.html
http://office.microsoft.com/en-us/ex...in=HP010079186