CODEWITHSUNDEEP
excelexcel-formula
Anish
Anish

Reputation: 758

Finding matching data and sum in Excel

I have an automated report with the following columns:

enter image description here

I need to sum the "offered" for every AMPO call offered at the same time. So from the above example, FS_AO_AMPO has 9 offered at 3/5/16 15:00 and FS_TRANS_AMPO has 2 offered at the same time. So total AMPO at 3/5/16 15:00 should be 11.

But I've been really struggling to get that working mainly because the first column data is merged into cells and is difficult to map into each individual entry in the DateTime column.

I can try quick and dirty ways by unmerging the cells but I'm looking for like an automated process, as there will be new reports coming in everyday.

Any help will be appreciated. Thanks in advance.

Upvotes: 1

Views: 79

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use The SUMPRODUCT() with a LOOKUP() to find the correct Call type:

=SUMPRODUCT((ISNUMBER(SEARCH("AMPO",LOOKUP(ROW($A$3:$A$13),ROW($A$3:$A$13)/($A$3:$A$13<>""),$A$3:$A$13))))*($C$3:$C$13)*($B$3:$B$13=5))

enter image description here

There are three parts to this formula:

The first: (ISNUMBER(SEARCH("AMPO",LOOKUP(ROW($A$3:$A$13),ROW($A$3:$A$13)/($A$3:$A$13<>""),$A$3:$A$13)))), finds the all the cells that have "AMPO" in them

The second: ($C$3:$C$13) is the area of cells to add.

The third: ($B$3:$B$13=5) finds only those where column B is 5. In your case you would use the date desired in lieu of 5.

It is finding where the first and third are true and adding the corresponding cells in Column C.

Upvotes: 1

Related Questions