CODEWITHSUNDEEP
excelvba
Dale Swire
Dale Swire

Reputation: 15

Summing up a column based on multiple Criterion

Basically

Column 1 contains many names,
Column 2 contains the Unique version of those many names,
Column 3 contains either 1 or 0

I want to sum the values in Column 3 based on Column 1 matching Column 2

EG

A        B        C
VBA     VBA       1
VBA     XY        0
XY      ZX        1
ZX                1
XY                1
VBA               0
XY                1
ZX                1

So I want it to produce a 4th column equal in length to Column B as follows

VBA    1
XY     3
ZX     2

I've tried a few different ways and I just cant seem to get it to work. Any help would be much appreciated

Upvotes: 0

Views: 34

Answers (2)

Dale Swire
Dale Swire

Reputation: 15

Anyone looking for another method aside from the above SUMIF method:

If its all on the Same sheet:

Cells(I, "C").Value = Application.CountIfs(Range("B:B"), Cells;
(I, "A").Value, Range("D:D"), "1")

If its on multiple sheets:

Sheets("SheetName").Cells(I, "C").Value = 
Application.CountIfs(Sheets("SheetName").Range("B:B"), Sheets("SheetName");
.Cells(I, "A").Value, Sheets("SheetName").Range("D:D"), "1")

There is probably a neater way of doing it. You could set a variable for the sheet names rather than typing Sheets("Sheetname") over and over.

Upvotes: 0

Mrig
Mrig

Reputation: 11702

You can use

=SUMPRODUCT(($C$1:$C$8)*($A$1:$A$8=B1))

Drag/Copy down as required. Change range as needed. See image for reference.

enter image description here

Upvotes: 2

Related Questions