EXCEL 2016 - SUM FORMULA - HOW TO AUTOMATICALLY ADD MULTIPLE ROWS MEETING NOMINATED CRITERIA 1

[Megs628]

Hi - Using Excel 2016

Use a spreadsheet to complete a comparison with our receipting vs bank settlements.

The bank provides simply a credit summary total for each site for the previous days transactions. This can be made up of multiple transactions within out receipting spreadsheet.

What I am hoping to set up is a sum formula that totals 1 or multiple values meeting nominated criteria across one or multiple rows.

Best explained in an example (refer to attachment)
Review column J for the same values, Review column D <> "DD", Review column A for same date values
when above 3 criteria are met then sum column H for the same number of rows where criteria is met.

MULTIPLE LINE EXAMPLE
J93:J96 = ARE ALL THE SAME VALUE "LAMPTON"
D9396 = <> DD
A93:A96 = ARE ALL THE SAME DATE "09/02/2017"

If all the above criteria are met then sum H93:H96

SINGLE LINE EXAMPLE
J91:J91 = VALUE = "LAMPTON"
D9191 = <> DD
A91:A91 = DATE "09/02/2017"

If all the above criteria are met then sum H91:H91

I'm not sure how to force Excel to review each column and row to meet the 3 criteria and then sum the values within only the rows meeting this criteria.

Thank you for your assistance

 

[SkipVought]

Hi,

Used a Pivot Table. Took less than 5 minutes.
Filtered Type to exclude "DD"
[pre]
Sum of AMOUNT
BANK DATE TYPE LOCATION Total
2/8/2017 EFT LAMPTON $33.00
[highlight #FCE94F]2/8/2017 Total $33.00[/highlight]
2/9/2017 CRED LAMPTON $900.00
RECMER LAMPTON $13.50
[highlight #FCE94F]2/9/2017 Total $913.50[/highlight]
2/10/2017 EFT LAMPTON $425.00
[highlight #FCE94F]2/10/2017 Total $425.00[/highlight]
2/13/2017 CRED MOLLE $450.00
EFT MOLLE $440.00
RECMER MOLLE $6.75
[highlight #FCE94F]2/13/2017 Total $896.75[/highlight]
Grand Total $2,268.25
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's your workbook with a solution.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Megs628]

Thanks! So simple! Clearly couldn't see wood for trees with a formula fixation

 

[SkipVought]

Thx.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]