COuntif / Sumproduct issue

**Spanion** · 01-05-2010, 08:03 AM

Hi.

I am creating a workbook for work.

The main part I am having trouble with is finding a formula that will sum, when two specific words are entered in two separate fields.

For Example...

Column A has a drop down list, with these options: A, B, C, D
Column B has a drop down list, with these options: 1, 2, 3, 4

What i want to do is create a table like this:

A B C D

1
2
3
4

So when column A has option A selected, and column B has option 1 selected, i want it to count in the table.

I want to be able to enter possible combination's multiple times as i go down the workbook, and i would like the table to continue to add up on a separate workbook. Ive tried countif and sumproduct but if i enter the same combination more that once it adds up incorrectly.

I hope this makes sense

Thanks

**DonkeyOte** · 01-05-2010, 08:07 AM

The most obvious solution is to use a Pivot Table based on your source data such that:

Field 1 is ROW field/label
Field 2 as COLUMN Field/Label
Field 2 as DATA field set to COUNT

...this will generate your matrix... if needed you can use a Dynamic Named Range as source for PT.

**Spanion** · 01-05-2010, 08:20 AM

Hmmm, ok...

My knowledge of excel is fairly limited, here is an example of some of the formulas i was trying to get it to work. That obviously didnt work... XP

=countif((A5:A10,"A")+countif(B5:B10,"1"))

=sumproduct(--(A5:A10,"A")--(B5:B10,"1"))

SO as i enter the options in to columns of a and b, it counted up in my table.

**DonkeyOte** · 01-05-2010, 08:32 AM

If you want to use formulae you will need to use SUMPRODUCT* - COUNTIF will not suffice.

For pointers as to how to use SUMPRODUCT see link in my sig. to Bob Phillips' white paper on the function.

(*if not using additional concatenation)

**shaundbrown** · 01-05-2010, 08:41 AM

Would a macro which does the following work for you?

You have the two drop down lists, and a button.

You select the two options, then click the button (to run the macro)

The macro takes the value of the two dropdown lists and logs them on a seperate sheet.

You could then have a count function to tell you how many of each you have.