Sum with multiple criteria

[mnunan]

Hi

Can anyone help with the following formula please, I need to:

Sum the cells in column G where the date in column D is between the dates specified in cells B2 and C2 (in a different worksheet) and where the text in column B is "LHR" and where the the cells in column L are not blank.

Column D = date
Column G = currency
Columns B & C in a different worksheet = date
Column L = text

Thanks very much

 

[T. Valko]

Hi

Can anyone help with the following formula please, I need to:

Sum the cells in column G where the date in column D is between the dates specified in cells B2 and C2 (in a different worksheet) and where the text in column B is "LHR" and where the the cells in column L are not blank.

Column D = date
Column G = currency
Columns B & C in a different worksheet = date
Column L = text

Thanks very much

What version of Excel are you using?

 

[Aladin Akyurek]

Hi

Can anyone help with the following formula please, I need to:

Sum the cells in column G where the date in column D is between the dates specified in cells B2 and C2 (in a different worksheet) and where the text in column B is "LHR" and where the the cells in column L are not blank.

Column D = date
Column G = currency
Columns B & C in a different worksheet = date
Column L = text

Thanks very much

Try on the sheet housing the relevant ranges...

=SUMPRODUCT(
    $G$2:$G$400,
    --($D$2:$D$400>=Sheet2!B2),
    --($D$2:$D$400<=Sheet2!C2),
    --($L$2:$L$400="LHR"))

If you are on Excel 2007 or later, try SUMIFS function...

 

[mnunan]

Sorry I always forget that! Excel 2007

 

[Aladin Akyurek]

Sorry I always forget that! Excel 2007

As pointed out in post #3, you can try a SumIfs formula on 2007 and later...

=SUMIFS(
    $G$2:$G$400,
    $D$2:$D$400,">="&Sheet2!B2,
    $D$2:$D$400,"<="&Sheet2!C2,
    $L$2:$L$400="LHR")

 

[T. Valko]

Sorry I always forget that! Excel 2007

OK, try this...

Use cells to hold the criteria:

• Sheet2 B2 = lower date boundary
• Sheet2 C2 = upper date boundary
• A2 = LHR

Then:

=SUMIFS(G2:G100,D2:D100,">="&Sheet2!B2,D2:D100,"<="&Sheet2!C2,L2:L100,A2)

 

[T. Valko]

As pointed out in post #3, you can try a SumIfs formula on 2007 and later...

=SUMIFS(
    $G$2:$G$400,
    $D$2:$D$400,">="&Sheet2!B2,
    $D$2:$D$400,"<="&Sheet2!C2,
    $L$2:$L$400="LHR")

Looks like you have a syntax error in that formula.

Try it like this:

=SUMIFS($G$2:$G$400,$D$2:$D$400,">="&Sheet2!B2,$D$2:$D$400,"<="&Sheet2!C2,$L$2:$L$400,"LHR")

 

[mnunan]

Hi

I have tried both the sumproduct and sumifs, sumproduct returns 0 and sumifs says too few arguments. Also, the last part of the formula you have suggested should be referencing column B not L and there doesn't seem to be an argument to check if column L is not empty?

Thanks again

 

[mnunan]

Looks like you have a syntax error in that formula.

Try it like this:

=SUMIFS($G$2:$G$400,$D$2:$D$400,">="&Sheet2!B2,$D$2:$D$400,"<="&Sheet2!C2,$L$2:$L$400,"LHR")

Thanks T Valko, your correction worked, however can you help me with also checking to see if column L (L should have been B in the above formula) contains any text, I don't want the corresponding cell in column G summed if column L doesn't contain anything.

Thanks

 

[T. Valko]

Thanks T Valko, your correction worked, however can you help me with also checking to see if column L (L should have been B in the above formula) contains any text, I don't want the corresponding cell in column G summed if column L doesn't contain anything.

Thanks

=SUMIFS($G$2:$G$400,$D$2:$D$400,">="&Sheet2!B2,$D$2:$D$400,"<="&Sheet2!C2,$L$2:$L$400,"LHR")

Are you saying that ...$L$2:$L$400,"LHR"... should be ...$B$2:$B$400,"LHR"... ?

Maybe this:

=SUMIFS($G$2:$G$400,$D$2:$D$400,">="&Sheet2!B2,$D$2:$D$400,"<="&Sheet2!C2,$B$2:$B$400,"LHR",$L$2:$L$400,"?*")