SUMIF IF referred sheet/column ISDATE

Hey everyone,

working on something that i am having a problem with

I have the following

=SUMIF({FSM - Help Desk Ticket Tracker Range 4}, $VlookupValue@row, {FSM - Help Desk Ticket Tracker Range 1})

This returns the value from the cell that i want to perform a specific calculation. I want to add more to this though. The SUMIF function should only return a value if a referenced sheet/column [Date Resolved] = isdate

Any Help would be greatly appreciated.(screen shots attached)

Find more posts tagged with

Sheets

Formulas

Support

Comments and Conversations

Cell linking

Comments

J. Craig Williams

Convert your SUMIF to SUMIFS. The syntax is different (I don't recommend using SUMIF at all)

=SUMIFS( range-to-sum, criteria-range-1, criteria-1, criteria-range-2, criteria-2, and so on)

Craig

CCBigelow

I appreciate the information. I believe this resolved my issue. I did not realize that the syntax for SUMIF and SUMIF was different until you mentioned it....i had tried to interchange them initially but obvi did not work.

formula is now as follows

=SUMIFS({FSM - Help Desk Ticket Tracker Range 1}, {FSM - Help Desk Ticket Tracker Range 2}, "Colton Bigelow", {FSM - Help Desk Ticket Tracker Range 4}, $VlookupValue@row)

It looks up the "Parts used qty" according to which person used which part and adds them into the field.

J. Craig Williams

I'm glad I helped.

While COUNTIF and COUNTIFS don't have the same swapped argument problem, I recommend always using COUNTIFS too.

Craig

CCBigelow

Craig, I appreciate the assistance maybe you could assist further

I have multiple sheet running this and the above formula worked for that sheet when i tried to use same formula (syntax) to reference for another sheet and changing the name i get #INVALID REF

=SUMIFS({DPaddPartQTY}, {DPtechConfirmed}, "David Prewitt", {DPPartUsedConfirm}, $VlookupValue@row)

I changed the name of the reference but they point to the same things and i changed the name

any ideas

CCBigelow

Craig, Please disregard this request. It appears it may have been a parsing issue with Smartsheet. I refreshed my page after saving it and it is now displaying as is should

J. Craig Williams

Please report your experience to support@smartsheet.com

I've given them examples of this behavior and there has been no noticeable improvement (which is difficult to say with a non-reproducible problem, but I said it anyway)

There is some sort of delay in the x-sheet referencing that needs to be addressed.

Craig

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…