SUMIFS with Named Ranges

[KeithS.]

I'm trying to learn more about the SUMIFS and COUNTIFS functions and am practicing with some hypothetical data. I have data about the number of gallons of paint sold in 4 regions.

In my hypothetical situation, I need to find out the number of gallons sold in the Northeast region during the month of August.

I have named ranges for most categories: "Date," "Gallons_Sold," "PaintColors," and "Region."

I am able to get a correct response if I use
=SUMIFS(Gallons_Sold,Region,"NE",Date,"August 21")
–but it is only info. for a single date. I need to get a total for the entire month of August.

=SUMIFS(Gallons_Sold,Region,"NE",Date,"August") returns zero. How do I get SUMIFS to evaluate "Date" for all August dates?

Any help is greatly appreciated!

 

[circledchicken]

I'm trying to learn more about the SUMIFS and COUNTIFS functions and am practicing with some hypothetical data. I have data about the number of gallons of paint sold in 4 regions.

In my hypothetical situation, I need to find out the number of gallons sold in the Northeast region during the month of August.

I have named ranges for most categories: "Date," "Gallons_Sold," "PaintColors," and "Region."

I am able to get a correct response if I use
=SUMIFS(Gallons_Sold,Region,"NE",Date,"August 21")
–but it is only info. for a single date. I need to get a total for the entire month of August.

=SUMIFS(Gallons_Sold,Region,"NE",Date,"August") returns zero. How do I get SUMIFS to evaluate "Date" for all August dates?

Any help is greatly appreciated!

Hi,

You could try using a wildcard character like this:
=SUMIFS(Gallons_Sold,Region,"NE",Date,"August*")

Although note:

• You shouldn't really enter dates as text as it becomes more difficult to work with them. It would be better to format the date values to look the way you want while maintaining the underlying component as an actual date so you can perform calculations, sort etc.
• Given the description in your post, this type of data summary is usually much more easily achieved with a PivotTable rather than formulae (although I understand it is just for your experimentation at this stage).
• For more on wildcard characters see:
  Wildcard characters - Excel - Office.com

 

[KeithS.]

Thanks. As for dates, I have formatted dates as "August 21, 2012" and created a named range - but in the formula bar it displays as 8/21/2012 - the way it was originally entered.

I just tried it w/ the wildcard (both with & w/o a space: "August*" and August *") and still get a zero.
Also just tried =SUMIFS(Gallons_Sold,Region,"NE",Date,"8/*") and got the same.

 

[rallcorn]

So, what if you use a separate column with this formula that points to the date cell:
=text(a1,"mmmm")
Copy it down & then redefine your "Date" named range to be the column with the formula?

 

[Aladin Akyurek]

I'm trying to learn more about the SUMIFS and COUNTIFS functions and am practicing with some hypothetical data. I have data about the number of gallons of paint sold in 4 regions.

In my hypothetical situation, I need to find out the number of gallons sold in the Northeast region during the month of August.

I have named ranges for most categories: "Date," "Gallons_Sold," "PaintColors," and "Region."

I am able to get a correct response if I use
=SUMIFS(Gallons_Sold,Region,"NE",Date,"August 21")
–but it is only info. for a single date. I need to get a total for the entire month of August.

=SUMIFS(Gallons_Sold,Region,"NE",Date,"August") returns zero. How do I get SUMIFS to evaluate "Date" for all August dates?

Any help is greatly appreciated!

If you let the Date range house true dates, you can easily invoke:

=SUMIFS(
     Gallons_Sold,
     Region,"NE",
     Date,">="&DATE(2012,8,1),
     Date,"<="&EOMONTH(DATE(2012,8,1),0))

You can of course enter =DATE(2012,8,1) or 1-Aug-12 in a cell of its own, say, Z2, and invoke:

=SUMIFS(
     Gallons_Sold,
     Region,"NE",
     Date,">="&Z2,
     Date,"<="&EOMONTH(Z2,0))

 

[KeithS.]

So, what if you use a separate column with this formula that points to the date cell:
=text(a1,"mmmm")
Copy it down & then redefine your "Date" named range to be the column with the formula?

Yes - that works. Thanks.

I'm trying to keep it as simple as possible since I'm working with high schoolers who are just learning Excel (and I'm not much further ahead of them myself!) We've just learned about IF, SUMIF, and COUNTIF and are trying to create some practice activities.

 

[circledchicken]

Yes - that works. Thanks.

I'm trying to keep it as simple as possible since I'm working with high schoolers who are just learning Excel (and I'm not much further ahead of them myself!) We've just learned about IF, SUMIF, and COUNTIF and are tryiong to create some practice activities.

As an aside you might find this YouTube channel useful for your students (and yourself):
excelisfun -- 1900 Excel How To Videos - YouTube

There are a huge range of examples provided within the tutorials. Here is one on the SUMIF function for example:
Excel Magic Trick #203: SUMIF function formula 21 Examples - YouTube

 

[KeithS.]

As an aside you might find this YouTube channel useful for your students (and yourself):
excelisfun -- 1900 Excel How To Videos - YouTube

Yes - I've discovered the ExcelIsFun channel - thanks for the heads up!

So ... is the reason my formula can't find "August" or "August*" because the dates aren't "real" dates?

[I guess I could format my dates as "General" and use (...Date,"41128")]

 

[barry houdini]

Did you see Aladin's suggestion? That would be my preference too......

 

[KeithS.]

Did you see Aladin's suggestion? That would be my preference too......

Yes - I've been playing with it. I want to use something similar for COUNTIF, so it should be pretty simple.
Thanks for all the help!