Trying to SUM with Multiple criteria

[Ignition1]

Hi

I have 2 columns on Sheet 1 -

Column A

BSE
EST
NWD
BSE
MNS
BSE

etc

Column B

01/02/2011
01/02/2008
08/06/2012

(etc...various dates)

Column C

2100
1292
1828
1822

(etc...various values)

What I want to do is SUM the values in column C that are BSE and equal to or before a certain date which is in cell D56 (the date in the cell is 01/02/2011 and is formatted to be date).

For example this is one I've tried -

SUM(IF(AND(A1:A50="BSE", B1:B50<=D56)),C1:C5)

This seems like a simple task, maybe I'm just being slow...

 

[Andrew Poulsom]

Try:

=SUMPRODUCT(--(A1:A50="BSE"),--(B1:B50<=D56),C1:C50)

 

[VoG]

Try

SUMPRODUCT(--(A1:A50="BSE"),--(B1:B50<=D56),C1:C50)

 

[cjcobra]

=sumifs(c117:c128,a117:a128,"bse",b117:b128,"<5/1/2000")

 

[Ignition1]

Dammit - returned a #VALUE error.

So this is what I put in -

=SUMPRODUCT(--('BNI All Prospects - REFERENCE'!B2:B1016=DATA!A2), --('BNI All Prospects - REFERENCE'!J2:J1020<=DATA!B53), 'BNI All Prospects - REFERENCE'!H2:H1021)

I should start being exact now -

BNI All Prospects - REFERENCE sheet has the NWD, BSE etc. J2:Jxxx has the dates, DATA sheet B53 has the date I want to find equal or before than.

H2:Hxx is the Gross Income values.

What could've returned the VALUE? Should I check all the rows to make sure they are all numbers etc?

 

[VoG]

The ranges all have to be the same number of rows.

 

[Andrew Poulsom]

Each argument must have the same number of rows. You have:

B2:B1016
J2:J1020
H2:H1021

 

[Ignition1]

Ahh...thanks very much!

Solved.

Edit - Just out of curiosity - why did I have to put in the -- between the arguments?

 

[Andrew Poulsom]

They coerce TRUE/FALSE into 1/0 so that they can be multiplied.