Count and addition formulas

[Finalfrontier1976]

Hi guys,

I have a spreadsheet (attached) and in it I have a lot of raw data. What I want to do is this:

I've started a table in column K and what I want to do is put a formula in under the three headings for each month (KM, Tonnes & Trips). What I want the formulas to do is if column E contains the word WASTE and column A also contains a date in January then add the all the KM's in column G for January and put that in column L for January. Then using the same criteria but adding up all the tonnes in column F put that in the January total in column M. Where it states Trips in column N, trips would be the count of every line that is for January (providing that column A contains a date in January for 2014 and the word WASTE appears in column E).

I'm sure then I can duplicate it for the rest of the months as I need this for the whole of 2014.

Any help would be much appreciated.

Thanks,
Lew

[Special-K]

Column G contains the word VOID so this will create an error with SUMPRODUCT, that column must all be numbers
Move the word VOID to another column and this will work

in L5
=SUMPRODUCT((TEXT(A2:A50000,"mmmm")=K5)*(G$2:G$50000))

in M5
=SUMPRODUCT((TEXT(A2:A50000,"mmmm")=K5)*(F$2:F$50000))

in N5
=SUMPRODUCT(--(TEXT(A2:A8,"mmmm")=K5)*(E$2:E$8="WASTE"))

and copy down the columns

[MarvinP]

Hi Star Trek Fan - Lew


A Pivot Table might do exactly what you need. See the attached. You can group the dates, like I've done or filter them for only showing 2014.


Waste Filter in Pivot Table.xlsx

I needed to remove 2016 dates to make the sheet small enough to attach. I hope you get the idea.

Time to learn Pivot Tables? http://www.excelfunctions.net/Excel-...-Tutorial.html

[Finalfrontier1976]

Thanks but that doesn't seem to work I just get #VALUE! could it be that the data in A:A is in date format?

[Special-K]

My mistake I was still testing column N

1. Move the VOIDs in column G to another column or you will get a #VALUE error

2.
in L5
=SUMPRODUCT((TEXT(A2:A50000,"yyyy")+0=$K$4)*(TEXT(A2:A50000,"mmmm")=K5)*(G$2:G$50000))
in M5
=SUMPRODUCT((TEXT(A2:A50000,"yyyy")+0=$K$4)*(TEXT(A2:A50000,"mmmm")=K5)*(F$2:F$50000))
in N5
=SUMPRODUCT(--(TEXT(A2:A50000,"yyyy")+0=$K$4)*(TEXT(A2:A50000,"mmmm")=K5)*(E$2:E$50000="WASTE"))

copy down the columns

[sandy666]

maybe this way
done with PowerQuery

btw. each column should have the same type of data, not mixed

[Finalfrontier1976]

Thanks guys, works a treat, much appreciated