Sum values in Column A, based on dates in Column B & if value in Column C = True

Hi All,

Like the title says, I would like a formula that adds up the values in Column A IF the Date in Column B is within a date range & value in Column C = TRUE.

The date range would be entered in 2 separate cells.

The cells in Column B are formatted as dd/mm/yyyy. Some of the cells in Column B are blank so I would assume the formula would ignore these.

I have tried the SUMIFS function but it didn't work for me. Maybe I need a VBA script or can it be done with formula?

Any help appreciated.

Try

=SUMIFS(A:A,B:B,">="&E1,B:B,"<="&F1,C:C,TRUE)

E1 = Start Date
F1 = End Date

Thanks for reply Jonmo,

I tried that formula already and it didn't work for me. If doesn't give an error, just return a zero value.

Either the values in B (and or your start end dates) are not really dates, they're just text strings that look like dates
Or the values in A are not real numbers, they're 'Numbers Stored As Text"

What do these return

=ISNUMBER(E1)
=ISNUMBER(F1)
=ISNUMBER(A1) <- filled down to the end
=ISNUMBER(B1) <- filled down to the end


It would also be helpful if you could attach a sample book.

Thanks again Jonmo,

Unfortunately the WB contain a lot of sensitive information. I can try removing this information if it helps.

In answer to the above ISNUMBER queries:

A1 = TRUE
B1 = FALSE
E1 = TRUE
F1 = FALSE

It seems like you have found the problem. Should all these cell be TRUE?

Yes, they should all be true.

What exactly is in column B, and F1 ?

As far as attaching a book, we don't need to see much.
Make a copy of the book, and delete everything except the values relative to the formula, substitute real names/account#s/etc with fake names/account#s/etc
And we only need maybe 10 rows worth of data.

It looks to be working fine in the book you posted.

Ok thanks, why is it giving ISNUMBER A2 = TRUE, B2 = FALSE?

Something happened to your formula in B3, it's NOT doing ISNUMBER on B2
It actually looks like =ISNUMBER(#REF!)

Change that to =ISNUMBER(B2) and it's TRUE.

Ok thanks for your help. I reformatted all cell in the main WB and seems to be working now.

You're welcome.