How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Russell H. Your Own Question
Russell H.
Russell H., Computer Systems Expert
Category: Microsoft Office
Satisfied Customers: 10630
Experience:  13+ years experience
13568747
Type Your Microsoft Office Question Here...
Russell H. is online now
A new question is answered every 9 seconds

I have a worksheet and am trying to summarize information in

Customer Question

I have a worksheet and am trying to summarize information in another worksheet using the SUMIFS function. I am trying to add a column for DH if the entries occur between a date range. Here is the formula: =SUMIFS('ADVERTISER SUMMARY'!P3:$P$34,'ADVERTISER SUMMARY'!B3:$B$34,A5,'ADVERTISER SUMMARY'!Q3:$Q$34,">=10/01/2016",'ADVERTISER SUMMARY'!$R$3:$R$34,<=10/31/2016"). I can not get the value to calculate.
JA: What kind of computer do you have?
Customer: HP with Excel 2010
JA: Have you installed any updates recently?
Customer: The computer was recently updated to Windows 10.
JA: Anything else you want the Microsoft Office expert to know before I connect you?
Customer: No
Submitted: 8 months ago.
Category: Microsoft Office
Expert:  Russell H. replied 8 months ago.

Hi, thank you for contacting JustAnswer.com. My name is Russell. I will do my best to provide the right answer to your question.

Was the other, 'remote', sheet created in the same MS Excel 2010 version, on the same computer?

Is the other 'remote' sheet, referenced in that formula, really named

ADVERTISER SUMMARY

with a space ?

What error message do you get from that formula, please?

Customer: replied 8 months ago.
The information is in a workbook and ADVERTISER SUMMARY is one of the worksheets. The name does have a space and is labeled on the tab: ADVERTISER SUMMARY. There is not error message, i just don't get any value but - returned & I know what the total should be. If I delete the second date range from the formula and change the start date to "<=10/31/2016", I get an answer that is correct.
Customer: replied 8 months ago.
Would it be easier for you to securely connect or do a face time session?
Expert:  Russell H. replied 8 months ago.

Let me think about this for a minute or three... perhaps a remote control session might help, but I'm not sure at this point.

Customer: replied 8 months ago.
Not for the extra money. I looked on the internet and a SUMIFS function with a date range appeared to somehow use the & symbol, but I am not sure how the syntax should read.
Expert:  Russell H. replied 8 months ago.

Try this please:

=SUMIFS('ADVERTISER SUMMARY'!P3:$P$34,'ADVERTISER SUMMARY'!B3:$B$34,A5,'ADVERTISER SUMMARY'!Q3:$Q$34,">=10/01/2016",'ADVERTISER SUMMARY'!$R$3:$R$34,"<=10/31/2016")

also try this please:

=SUMIFS('ADVERTISER SUMMARY'!P3:$P$34,'ADVERTISER SUMMARY'!B3:$B$34,A5,'ADVERTISER SUMMARY'!Q3:$Q$34,">=10/1/2016",'ADVERTISER SUMMARY'!$R$3:$R$34,"<=10/31/2016")

One of those two ought to work, I think... though without seeing the sheet(s) I cannot be quite sure. At any rate the second date has no initiating double-quote, a mistake for sure.

Let me know how that goes! and which of the two works and works properly, etc., please. Thanks.

Customer: replied 8 months ago.
Neither one of the formulas worked. They did return a value of 0.
Expert:  Russell H. replied 8 months ago.

They both returned

0

...? if so, then the formulas worked... if you got no

0

and just a

-

without the double-quote around the second of those two dates, then that (lack of surrounding double-quote pair, around the second date) was the main problem.

OK... now, if you omit the second date (and its double-quotes too, whether one pair of double-quotes or both) what value do you get?

Let me know, please... I'm thinking about it on this side, also.

Customer: replied 8 months ago.
If I delete the second criteria range and the criteria ("=<10/31/2016"), I get the correct answer. There is one total in the amount column of $200 for DH that is ">=10/01/2016" in the start date column. The question now is how do we add the criteria range and criteria to get matches that are also <=10/31/2016 from the expiration date column????
Expert:  Russell H. replied 8 months ago.

Do you have any values that are above 10/31/2016 date ?

And what is the overall intention of the SUMIFS formula? what is it supposed to do basically, in words?

I think that perhaps you might need to review SUMIFS ?? and here's a good clear summary of how it is used, *if* you need it:

https://exceljet.net/formula/sum-if-date-is-between

Customer: replied 8 months ago.
I want to sum values for an individual from the monthly column that show a start date of greater than a certain value and an expiration date of less than a certain value. i.e. a start date >=10/01/2016 and an expiration date of <=10/31/2016 would provide the sum of the monthly column values for DH for October 2016
Customer: replied 8 months ago.
The start date and expiration dates are in separate columns.
Expert:  Russell H. replied 8 months ago.

Thanks for the explanation.

The SUMIFS function is arranged as follows, in general terms:

=SUMIFS(amount,date,">"&H5,date,"<"&H6)

in other words,

SUM (add up total for all) of

amount

that are

date > H5

and

date < H6

where H5 holds the start date, and H6 holds the end date.

Now in your case, there is an A5 reference, and I don't know what that is supposed to be.

In practice that formula would look like this, literally and not generally:

=SUMIFS(D5:D11,C5:C11,">"&H5,C5:C11,"<"&H6)

Expert:  Russell H. replied 8 months ago.

=SUMIFS(monthlycolumnrange, datesrange, ">=10/01/2016", datesrange, "<=10/31/2016")

is I think what you're aiming at, if what you explain is correct. That will sum/total the amounts in the monthly column range specification, for the dates range, of between and including the start of Oct. of this year and the end of Oct. of this year.

Customer: replied 8 months ago.
A5 has a value of DH (salesperson) in sheet 1. The remainder of the iinformation is in the AD SUM worksheet: Column P contains the monthly amounts, Column Q contains the start date, and Column R contains the expiration date.
Expert:  Russell H. replied 8 months ago.

Sheet 1 is where the formula (as well as A5) is, correct?

Customer: replied 8 months ago.
Customer: replied 8 months ago.
I thought I had the parameters and syntax you are talking about in this formula, but it returns a value of 0: =SUMIFS('ADVERTISER SUMMARY'!P3:$P$34,'ADVERTISER SUMMARY'!B3:$B$34,A5,'ADVERTISER SUMMARY'!Q3:$Q$34,">=10/01/2016",'ADVERTISER SUMMARY'!$R$3:$R$34,"<=10/31/2016")
Customer: replied 8 months ago.
I need to run to an appointment; do you have a phone number that I can call if I can not get the formula to work?
Expert:  Russell H. replied 8 months ago.

OK.

Then

=SUMIFS(monthlycolumnrange, datesrange, ">=10/01/2016", datesrange, "<=10/31/2016")

versus

=SUMIFS('ADVERTISER SUMMARY'!P3:$P$34, 'ADVERTISER SUMMARY'!B3:$B$34, A5, 'ADVERTISER SUMMARY'!Q3:$Q$34, ">=10/01/2016", 'ADVERTISER SUMMARY'!$R$3:$R$34, "<=10/31/2016")

you can see that A5 name of salesperson, has no place in a SUMIFS formula.

I thinkyou need to make the formula more diverse, e.g. nest it within an IF as to the name of the salesperson... or something.

Just come back to this case here:

http://www.justanswer.com/microsoft-office/9xszj-worksheet-trying-summarize-information.html

Customer: replied 8 months ago.
It works with the A5 reference when only using the start date. Why can't the expiration date parameter be added?
Expert:  Russell H. replied 8 months ago.

Try omitting the A5 !:

=SUMIFS('ADVERTISER SUMMARY'!P3:$P$34, 'ADVERTISER SUMMARY'!B3:$B$34, 'ADVERTISER SUMMARY'!Q3:$Q$34, ">=10/01/2016", 'ADVERTISER SUMMARY'!$R$3:$R$34, "<=10/31/2016")

Customer: replied 8 months ago.
I was able to get it to work just using one date parameter. I will try your suggestion when I need two date parameters.
Thanks for your help............dan'l.....
Expert:  Russell H. replied 8 months ago.

Excellent!

Please rate my work and my answer - recall that I caught the lack of paired double-quotes - so that this case may be closed. A five-star rating would be appreciated!

Thanks.

Related Microsoft Office Questions