CountIF Values (Text) related to a Cell

[GoranH]

I need to count how many times an item went from a certain source, and afterwards from which source it went the most.
Say...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Source[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]Wallmart [/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Wallmart [/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Newegg[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Wallmart [/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Wallmart [/TD]
[TD]Apple[/TD]
[/TR]
</tbody>[/TABLE]


How do I, on a new sheet add next to the cell with value "Apple", columns Wallmart/Target/Newegg, and how many times they appeared related to the value "Apple", or oranges etc.

And/or, just return the "Source" that appeared the most related to the value Apple.
If needed I can provide an actual file with example.

Regards

 

[tyija1995]

Hey,

I'd make a column for each source, say A1 = wallmart, B1 = Newegg, C1 = Target, I'd put an item column in D which lists the unique items (apple/orange) etc

Then use a SUMPRODUCT formula as such:

A2:

SUMPRODUCT((A$1='YourTabName'!$A$2:$A$8)*('YourTabName'!$B$2:$B$8=$D2))

Where A2:A8 and B2:B8 contain the data listed in the example above on the sheet 'YourTabName' - change as appropriate (as you're using a second sheet to write the formula)

 

[GoranH]

Hey,

I'd make a column for each source, say A1 = wallmart, B1 = Newegg, C1 = Target, I'd put an item column in D which lists the unique items (apple/orange) etc

Then use a SUMPRODUCT formula as such:

A2:

SUMPRODUCT((A$1='YourTabName'!$A$2:$A$8)*('YourTabName'!$B$2:$B$8=$D2))

Where A2:A8 and B2:B8 contain the data listed in the example above on the sheet 'YourTabName' - change as appropriate (as you're using a second sheet to write the formula)

Works ok enough if I then add another column that returns header with max values like so
"=INDEX($A$1:$C$1,0,MATCH(MAX($A2:$C2),$A2:$C2,0)) .... where A1:C1 are headers, and A2:C2 are returned values.

Aight works, though if anyone could make something that works in a single cell, it'd be great.

Thanks tyija