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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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,108,969
Messages
5,525,960
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top