SUM with Critera in 3 columns.

Chris1987

New Member
Joined
Oct 8, 2014
Messages
21
Hello all. I having been given a spreadsheet that I have been told I can make minimal changes to and i need to get this formula to work if possible? If i cant would i just have to add a new column with the year in?

The Formula ive been trying is =SUMPRODUCT(--('TASK DETAILS'!D1:D1000="2014"),--('TASK DETAILS'!E1:E5000="Jan"),--('TASK DETAILS'!AT1:AT1000="Y"))

Which isnt working :/

I have Three Columns with data in, and I only want it to count if if all 3 terms are met. Terms being "2014" in column D, "Jan" in column E and "Y" in column AT.

I apolagise for the bad spelling and gramma.

Your help would be so greatly received. :)
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

a massive zebra

Board Regular
Joined
Dec 1, 2009
Messages
87
I think you need consistent row ranges, so either 1 to 1000 or 1 to 5000, but not a mixture of both. If that doesn't work, does this: =SUMPRODUCT(('TASK DETAILS'!D1:D5000="2014")*('TASK DETAILS'!E1:E5000="Jan")*('TASK DETAILS'!AT1:AT5000="Y"))
 

a massive zebra

Board Regular
Joined
Dec 1, 2009
Messages
87
If you are using excel 2007 or later, you could just use COUNTIFS: =COUNTIFS('TASK DETAILS'!D1:D5000,2014,'TASK DETAILS'!E1:E5000,"Jan",'TASK DETAILS'!AT1:AT5000,"Y")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,367
Messages
5,486,440
Members
407,547
Latest member
Sankarasrinivas

This Week's Hot Topics

Top