sumifs question i should be able to get this working, but it is baffling me. I h

Question

sumifs question

i should be able to get this working, but it is baffling me. I have a sumifs equation=SUMIFS(density!E4:density!E5000,density!$B$4:density!$B$5000,Ortega!$A$3,density!$C$4:density!$C$5000,">.270833332",density!$C$4:density!$C$5000,"<.37500001") where i am checking for a specific date within a range (density!E4:density!E5000,density!$B$4:density!$B$5000,Ortega!$A$3) where ortega $a$3 is the date, and then looking for results that fit within time parameters (in this case between 6:30 am and 9:00 am). When i code it as it is shown above it works great in that the decimal numbers are the MS time indicator.   

But when i want to set the > and < to address a cell address, it does not work. I will show the equations i tried and both gave a value of "0".

=SUMIFS(density!E4:density!E5000,density!$B$4:density!$B$5000,Ortega!$A$3,density!$C$4:density!$C$5000,">B5",density!$C$4:density!$C$5000,"<C5") where B5 and C5 are the times (6:30 am and 9:00 am respectively).

I even tried to break the time into hours, minutes, seconds in separate cells using the =hour(B5), etc. and then using the following formula as if it might work.

=SUMIFS(density!E4:density!E5000,density!$B$4:density!$B$5000,Ortega!$A$3,density!$C$4:density!$C$5000,">time(D10,E10,F10)",density!$C$4:density!$C$5000,"<time(G10,H10,I10)")...

Needless to say it gave me the same zero result. and i repeat that the first formula listed above works fine and gives me the correct results, but i would prefer to use a cell reference so that the value is variable versus a constant in the formula..

thanks for help !!

Explanation / Answer

You wrote:

But when i want to set the > and < to address a cell address, it does not work.

[....]

I will show the equations i tried and both gave a value of "0".

=SUMIFS(density!E4:density!E5000,density!$B$4:density!$B$5000,Ortega!$A$3,density!$C$4:density!$C$5000,">B5",density!$C$4:density!$C$5000,"<C5")

[....]

=SUMIFS(density!E4:density!E5000,density!$B$4:density!$B$5000,Ortega!$A$3,density!$C$4:density!$C$5000,">time(D10,E10,F10)",density!$C$4:density!$C$5000,"<time(G10,H10,I10)").  

Try:

=SUMIFS(density!E4:density!E5000,density!$B$4:density!$B$5000,

Ortega!$A$3,density!$C$4:density!$C$5000,">" & B5,density!$C$4:density!$C$5000,"<" & C5)

and

=SUMIFS(density!E4:density!E5000,density!$B$4:density!$B$5000,

Ortega!$A$3,density!$C$4:density!$C$5000,">" & time(D10,E10,F10),

density!$C$4:density!$C$5000,"<" & time(G10,H10,I10))

Hope this help !!

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.