Selecting 2 different Values from the 1 spreadsheet

[silva1985]

Hi Guys,

Appologies if i sound quite inexperienced but I am wondering if any of you could help me with this request.

REFER TO THE ATTACHED SPREADSHEET.
Basically, I would like a excel formula that would:

a) Select Customer 'John' from Column A
b) Select Product Type 'Sugar' from Column B

.. to produce a count output of how many times this would occur.

I have extensive amount of data that I need to count but I seem to be having trouble with this as I have been mixing around with COUNTIF statements which all but seem to fail.

Any help we would be greatly appreciated.

Cheers,
Daniel

[teylyn]

Hi Daniel,

somehow your spreadsheet didn't make it, but you're probably looking at something like

=sumproduct(--($A$1:$A$100="John"),--($B$1:$B$100="Sugar"))

In Excel 2007 you could use the new COUNTIFS with several conditions, but in Excel 2003 Sumproduct is the easiest way.

hth

[silva1985]

Hi Teylyn,

Thank you for your reply, I have reattached the file below.
I'm using Excel XP just incase that helps out anymore.

Let me know what you come up with and if you could, can you please explain how the formula works?

Regards,
Daniel

[teylyn]

use the formula I posted above. Adjust the ranges to suit, but it works as is.

Read here about sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[silva1985]

For some reason it comes up with 0 - when fix this formulae up with my own data.

Maybe we can try a different way because my main group of data has 2 columns which have 2000 rows in each.

As an example Column A might have 230 different values which each occur more than once and in Column B it has 9 different values which each occur more than once.

Based on this dataset, I need a formula that can identify how many times a value 'x" occurs (more than once) in Column A when value 'y' in Column B comes up.

Hope this helps as I am finding this is a little hard to explain - might have to submit a group of the data i have.

Thanks for your time with this mate.

Regards,
Daniel

[teylyn]

Daniel, your spec was: count how many times John has bought sugar. The formula counts this without a flaw on the data you provided.

Please explain what is not working. I have the feeling that you want something different from what you detailed.

Do you want for each individual name in column A to count how often they bought each individual item in column B?

In that case, you may be better off with a pivot table.

post some of your "real" data and explain the full extent of your requirements.

china plate.