Sum the range of data if two conditions are satisfied

Hello Guyz,

This is my first post, I might make some mistakes in here.

My data is as follows

Month Miles Amount Location
June 8 £3.20 Loc1
June 8 £3.20 Loc1
June 8 £3.20 Loc2
July 8 £3.20 Loc2
July 8 £3.20 Loc1
July 38 £15.20 Loc1
July 4 £1.60 Loc2
Aug 38 £15.20 Loc2
Aug 4 £1.60 Loc1
Aug 8 £3.20 Loc1

Here is what I want to do

- Avoide filtering and manually calculating data for the total expense in the month

I want to SUM all the (3)AMOUNT in the (1)MONTH of JUNE for
(4)LOCATION LOC1

I know its too much to ask for but can you suggest if i can combine SUMIF or SUM(IF) or any other formulas ???

thanks and cheers!!

Mihir

Thi is the formula that meet you need:

=SUM.IF(RANGE(FIRST CELL COLOUMN MONTH.:LAST CELL COLUMN MONTH);RANGE("WHERE YOU EDIT THE MONTH NAME YOU REFER TO");RANGE(FIRST CELL COLOUMN AMOUNT:RANGE LAST CELL COLOUMN AMOUNT)

It is necessary you edit in a cell, the name of the month you refer to. The formula give you the total amount as per name month edited

Sorry, I forgot an additional suggestion:

if you add a new column range with the following formula
=Range("A")&Range("D")

range("A)=Month name column
Range("D")=loc type

you join the two values


edit in a range the content of the target you are looking for:

JuneLoc1

with the formula I sent you before you get the total amount for the month @ the location selected.

Hello Jeff,

Not sure about sum.if or sumif; it was confusing.

but i have attached the file with the formula's i tried.

please have a look at it if you can spare some time.

thanks

Mihir

didnt attach earlier ...

BINGO!!!!!!

it works ... and i will drag then down or put the values accordingly ....

thanks for your help []

Mihir

this one's working fine too,

I am facing some problems with pivot table data while using it for another link.

Trying to figure out a fix to it.....

thanks,

Mihir

I attached your file duly modified.
It works.
I have added a new column with the code (month name & location type)
Month name: from the list lef side
& = join
Location type= from the "haeding of the table at right side"
Read the formula content in each cell of the right table and you understand
why it works.
Ciao
Jeff Italy

Not sure exactly what your linking other tables problem is, but a general tip:

If you click on a pivot table cell to reference it in a formula, Excel will put the "access the pivot table this way" syntax into the formula for you - this is very powerful, but much more often(!) you just want the cells in a column, typing in the cell reference (as opposed to clicking) will work and formulae made this way will copy across rows and columns as expected.

HTH