Sumifs Statement Returning a #value Sign

Hi, I am hoping someone can help me solve this.

I have six rows of data Date Field (01/2014) Vendor, Company, Product, Gross Profit and Revenue

In another worksheet i am trying to do a sumif based on a selected date range however my formula keeps returning a #value sign and i can't figure out why.

One one tab i have a date lookup (Data Validation) looking up the 1st column in my data sheet like this:
Start Date = 01/2014
End Date = 02/2014

In the data there are multiple data points with this date.

In the lookup field where my sumifs is i have it saying sum ifs where "3M" matches these date ranges but its returning a #value sign everytime. I've tried to troubleshoot and it still isn't working.

Here is an example of my formula = =SUMIFS(BigSumRange,BigRange1,$H50,BigDateRange,">="&$P$48,BigDateRange,">="&$R$48)

and a few rows of my data with an example of what i am trying to do. I am building a cascading drop dop in another workbook but i don't think that should change the sumif.
Sumif.xlsx

Can you post a book that actually includes the formula using the named ranges?
The formula in that book is using regular hard coded ranges.


FYI, the dates you have in column A are not really dates. They're just text strings.
Confirmed with
=ISNUMBER(A2) <- returns FALSE

Okay, so how do i fix this so that it works properly?

I tried to attached the sheet with the named ranges but its too large.

Originally Posted by krunk

Okay, so how do i fix this so that it works properly?

I don't know, need to see the formula using the named ranges.

The issue with the dates won't cause the formula to return #Value! error.
It would just result in an incorrect answer, probalby 0

Anyway, you can probably correct the dates by highlighting column A, Data - Text To Columns - Delminated - Finish
That should convert all the strings to real dates (each will be the first of each month)

But again, that's not the cause of the #Value! error

I think i just figured out why it won't work, its excel 2003! so if i want to use a sumproduct formula how would i do that?

If it was XL2003, you'd get the error #NAME?

My best guess at the #VALUE! error is that your named ranges are not of equal demensions.
They must all be the same size.

Anyway, sumproduct equivelent would be

=SUMPRODUCT(--(BigRange1=$H50),--(BigDateRange>=$P$48),--(BigDateRange<=$R$48),BigSumRange)

Nope still did not work..

Here are my named ranges =

BigRange1=INDIRECT("Cascading!"&ADDRESS(2,MATCH(MyDashboard!$H$49,Cascading!$1:$1,0))&":"&ADDRESS(COUNTA(Cascading!$A:$A),MATCH(MyDashboard!$H$49,Cascading!$1:$1,0)))

BigSumRange= =INDIRECT("Cascading!"&ADDRESS(2,MATCH(MyDashboard!$G$48,Cascading!$1:$1,0))&":"&ADDRESS(COUNTA(Cascading!$A:$A),MATCH(MyDashboard!$G$48,Cascading!$1:$1,0)))

BigDateRange = =OFFSET(Cascading!$A$2,0,0,COUNTA(Cascading!$A:$A),1)

Yep, they're not the same size.

Let's say COUNTA(Cascading!$A:$A) = 10 just for example
BirRange1 and BigSumRange use 10 directly as the end row #, so they end up with rows from 2 to 10

However, BigDateRange is using a different method to create the rows.
It offsets from A2 and creates a range that is 10 rows long.
So it ends up with rows beginning in Row 2, 10 rows long, so it goes from row 2 to 11


You need to decide which one is correct, my guess is BigRange1 and BigSumRange are the correct..
So subtract 1 from the COUNTA in the BigDateRange

or vice versa, ADD 1 to the counta in the other 2.

OMG you are a genus. thank you.

Glad to help, thanks for the feedback.