Sumifs

[Dark0Prince]

So I'm trying to make this SUMIFS show me results from July2016 through November2016

This one works

=SUMIFS(COLJUD!D:D,COLJUD!A:A,12,COLJUD!M:M,2016,COLJUD!O:O,$B$5)

This one doesn't bring any results and should

=SUMIFS(COLJUD!D:D,COLJUD!A:A,"<="&11,COLJUD!A:A,">="&7,COLJUD!M:M,2016,COLJUD!O:O,$B$5)

To explain the code a little bit I'm pulling results from the D column on COLJUD sheet for anything greater then month 11 that appears in the A column and Greater then Month 7 also in A column in the year 2016 that appears in M column. O column on COLJUD sheet must match a specific client number in $b$5. These are all true and still zero results.

 

[Dark0Prince]

Re: need help with SUMIFS

I've also tried this variation, and no results when it's written like this either.

=SUMIFS(COLJUD!D:D,COLJUD!A:A,"<=11",COLJUD!A:A,">=7",COLJUD!M:M,2016,COLJUD!O:O,$B$5)

 

[snjpverma]

Re: need help with SUMIFS

Try using sumproduct

like this (Try and avoid using the whole column in this formula or else the calculation time will be very high. Even the sheet can get hung.)
so instead of A:A , you can use the maximum possible range of your data. e.g. A1:A2000

=SUMPRODUCT(COLJUD!D:D*(COLJUD!A:A<=11)*(COLJUD!A:A>=7)*(COLJUD!M:M=2016)*(COLJUD!O:O=$B$5))

 

[Dark0Prince]

Re: need help with SUMIFS

I've also tried this variation, and no results when it's written like this either.

=SUMIFS(COLJUD!D:D,COLJUD!A:A,"<=11",COLJUD!A:A,">=7",COLJUD!M:M,2016,COLJUD!O:O,$B$5)

 

[Dark0Prince]

Re: need help with SUMIFS

Try using sumproduct

like this (Try and avoid using the whole column in this formula or else the calculation time will be very high. Even the sheet can get hung.)
so instead of A:A , you can use the maximum possible range of your data. e.g. A1:A2000

=SUMPRODUCT(COLJUD!D:D*(COLJUD!A:A<=11)*(COLJUD!A:A>=7)*(COLJUD!M:M=2016)*(COLJUD!O:O=$B$5))

This gives a #value error

 

[Dark0Prince]

Re: need help with SUMIFS

Try using sumproduct

like this (Try and avoid using the whole column in this formula or else the calculation time will be very high. Even the sheet can get hung.)
so instead of A:A , you can use the maximum possible range of your data. e.g. A1:A2000

=SUMPRODUCT(COLJUD!D:D*(COLJUD!A:A<=11)*(COLJUD!A:A>=7)*(COLJUD!M:M=2016)*(COLJUD!O:O=$B$5))

I fixed the value error. But it's still showing zero results.

=SUMPRODUCT(COLJUD!D1:D5000)*(COLJUD!A1:A5000<=11)*(COLJUD!A1:A5000>=7)*(COLJUD!M1:M5000=2016)*(COLJUD!O1:O5000=$B$5)

 

[Dark0Prince]

Re: need help with SUMIFS

This is solved the D column wasn't formatted as a a number