Microsoft Office
Microsoft Office Questions? Ask a IT Expert for Support ASAP
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?
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.
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 doublequote, a mistake for sure.
Let me know how that goes! and which of the two works and works properly, etc., please. Thanks.
They both returned
0
...? if so, then the formulas worked... if you got no
and just a

without the doublequote around the second of those two dates, then that (lack of surrounding doublequote pair, around the second date) was the main problem.
OK... now, if you omit the second date (and its doublequotes too, whether one pair of doublequotes or both) what value do you get?
Let me know, please... I'm thinking about it on this side, also.
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/sumifdateisbetween
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)
=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.
Sheet 1 is where the formula (as well as A5) is, correct?
OK.
Then
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/microsoftoffice/9xszjworksheettryingsummarizeinformation.html
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")
Excellent!
Please rate my work and my answer  recall that I caught the lack of paired doublequotes  so that this case may be closed. A fivestar rating would be appreciated!
Thanks.