Countif and sumif in case of 2 ranges with VBA

[vergab]

I can usethe countif and sumif functions in case of 1 range. But there are 2 ranges wherewithI have to work and I don’t have any idea. I would like to collect the uniqueconnect between elements of two ranges, count how many are them and how much isthe total of their values. See the example. Can anybody help me?

[TABLE="class: outer_border, ******* 142"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]A range[/TD]
[TD]B range[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]ee[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]bb[/TD]
[TD]ee[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]cc[/TD]
[TD]gg[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]ff[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]dd[/TD]
[TD]gg[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]bb[/TD]
[TD]ff[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]cc[/TD]
[TD]gg[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]ee[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]ff[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]ff[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

[TABLE="class: outer_border, ******* 245"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]unique connection between the elements of the ranges[/TD]
[TD]Quantity[/TD]
[TD]sum of values[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]ee[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]ff[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]bb[/TD]
[TD]ee[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]bb[/TD]
[TD]ff[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]cc[/TD]
[TD]gg[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]dd[/TD]
[TD]gg[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


<strike></strike>

 

[sheetspread]

the pivot table does this, or use sumifs/countifs

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]A range[/TD]
[TD]B range[/TD]
[TD]Value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]aa[/TD]
[TD]ee[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]bb[/TD]
[TD]ee[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]cc[/TD]
[TD]gg[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]aa[/TD]
[TD]ff[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]dd[/TD]
[TD]gg[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]bb[/TD]
[TD]ff[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]cc[/TD]
[TD]gg[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]aa[/TD]
[TD]ee[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]aa[/TD]
[TD]ff[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]aa[/TD]
[TD]ff[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Quantity[/TD]
[TD]sum of values[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]aa[/TD]
[TD]ee[/TD]
[TD]

2​

[/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]aa[/TD]
[TD]ff[/TD]
[TD]

3​

[/TD]
[TD]

13​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]bb[/TD]
[TD]ee[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]bb[/TD]
[TD]ff[/TD]
[TD]

1​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]cc[/TD]
[TD]gg[/TD]
[TD]

2​

[/TD]
[TD]

19​

[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]dd[/TD]
[TD]gg[/TD]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[/TR]
</tbody>[/TABLE]

c15=SUMPRODUCT(--($A$2:$A$11&$B$2:$B$11=A15&B15)) copy down

d15=SUMPRODUCT(--($A$2:$A$11&$B$2:$B$11=A15&B15),$C$2:$C$11) copy down

 

[vergab]

It is only an example what I put here. The real data base contains about 12.000 rows, and I need a macro code for it not only an Excel function. I have to count with all of unique connection.

 

[sheetspread]

The pivot table is your best choice:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	A range	B range	Value
2	aa	ee	2
3	bb	ee	3				A range	B range	Count of Value	Sum of Value2
4	cc	gg	12				aa	ee	2	9
5	aa	ff	4				aa	ff	3	13
6	dd	gg	5				bb	ee	1	3
7	bb	ff	6				bb	ff	1	6
8	cc	gg	7				cc	gg	2	19
9	aa	ee	7				dd	gg	1	5
10	aa	ff	5
11	aa	ff	4
Sheet3

it can be recorded in a macro also

 

[vergab]

Pivot is no good, because it has another surface than a spreadsheet I need. I need only the data from the Pivot in a spreadsheet.

 

[sheetspread]

You can set the destination to anywhere you like, and paste as a value if you want to format it differently. Or you can just apply the formulas to the unique pivot table A and B range elements.

 

[vergab]

Could you please show me an example?

 

[sheetspread]

Under "Choose where you want the PivotTable report to be placed" in the pivot table dialog box, click either the New or Existing Worksheet radio button and click on a single cell. Then drag fields into the sections you want (try different configurations if you need practice). Putting just the A and B ranges into Row Labels, then choosing tabular view in report layout-design will get you unique items. Or you can do this with the advanced filter, but then everything has to be on the same worksheet.