# Sumifs

#### KtomK

##### New Member
hi

i would like to know the formula for to consolidate data from Sheet 1.
outcome should be as in sheet 2.
 Sheet 1 A B C A B C 01.01.2019 1 1 1 1 02.01.2019 1 1 1 1 03.01.2019 1 1 1 Sheet 2 A B C 01.01.2019 2 2 0 02.01.2019 1 1 2 03.01.2019 1 1 1

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

<tbody>
</tbody>

Best,
TKT

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
try

Book1
ABCDEFG
1ABCABC
201.01.20191111
302.01.20191111
403.01.2019111
5
6ABC
701.01.2019220
802.01.2019112
903.01.2019111
Sheet5
Cell Formulas
RangeFormula
B7=SUMPRODUCT((\$A\$2:\$A\$4=\$A7)*(\$B\$1:\$G\$1=B\$6),\$B\$2:\$G\$4)

try

ABCDEFG
1ABCABC
201.01.20191111
302.01.20191111
403.01.2019111
5
6ABC
701.01.2019220
802.01.2019112
903.01.2019111

</tbody>
Sheet5

Worksheet Formulas
CellFormula
B7=SUMPRODUCT((\$A\$2:\$A\$4=\$A7)*(\$B\$1:\$G\$1=B\$6),\$B\$2:\$G\$4)

</tbody>

<tbody>
</tbody>

Thanks AlanY, this worked!

cheers

You’re welcome

Replies
0
Views
300
Replies
6
Views
348
Replies
7
Views
167
Replies
4
Views
176
Replies
3
Views
220

1,203,125
Messages
6,053,648
Members
444,676
Latest member
locapoca

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back