Sum of a cell dependant on the sum of another cells value.

I have two drop down lists adjacent to each other, both containing data validation to restrict input from a list.

The first drop down contains the options (Column H) - A,B,C,D,E
The second drop down contains (Column I) - 1,2,3,4,5

This is repeated on every row down to ~1000.

I want to make a function that will sum down to row ~1000 to show the following:

	A	B	C	D	E
1
2
3
4
5

So for example, if, within the 1000 rows, column H has seven drop down boxes that have 'A' and column I adjacent has 5 '1' and 2 '4', it would show as such:

	A	B	C	D	E
1	5
2
3
4	2
5

Any questions, please let me know. Thank you in advance.

Try something like this in A1:

=COUNTIF($H$1:$H$1000,CHAR(64+COLUMNS($A:A)),$I$1:$I$1000,ROWS($1:1))

Then copy across to E1, then down to row 5.

Hope this helps.

Pete

Originally Posted by Pete_UK

Try something like this in A1:

=COUNTIF($H$1:$H$1000,CHAR(64+COLUMNS($A:A)),$I$1:$I$1000,ROWS($1:1))

Then copy across to E1, then down to row 5.

Hope this helps.

Pete

Hi Pete, Thank you for your reply.

Unfortunately I'm presented with the error 'Too many arguments for this function'.

Sorry, it should be COUNTIFS, like this:

=COUNTIFS($H$1:$H$1000,CHAR(64+COLUMNS($A:A)),$I$1:$I$1000,ROWS($1:1))

Hope this helps.

Pete