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

1. ## 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.  Register To Reply

2. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

Try

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

E1 = Start Date
F1 = End Date  Register To Reply

3. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

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

4. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

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.  Register To Reply

5. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

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?  Register To Reply

6. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = 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.  Register To Reply

7. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

I did try to reformat cells but it half worked. Please find WB attached. I am trying to fill in the value in C2 on the 'Returns' sheet based on the Start and End Dates in A2 and B2. i.e. I want the sum of all invoices paid within that period.

PS, it seems to have worked now that I have removed a lot of info from the sheet however certain values are still FALSE.  Register To Reply

8. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

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

9. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

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

10. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

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.  Register To Reply

11. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

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

12. ## Re: Sum values in Column A, based on dates in Column B & if value in Column C = True

You're welcome.  Register To Reply