Invalid Data Type Error for Embedded AND statement within an SUMIF

I'm trying to sum project estimates based on department and type of project. Basically if the department is "Operations" and the type of project is "MC" then sum the estimates for those conditions? Also referencing another sheet.

I receive an "Invalid Data Type" using the formula below.

=SUMIF(AND({5 Year Capital Plan (LC MC & DC) Range 3}, ="MC", {5 Year Capital Plan (LC MC & DC) Range 1}), ="Operations", {5 Year Capital Plan (LC MC & DC) Range 2})

Find more posts tagged with

Accepted answers

Gary Gazankas

Ahhh, that did it!!

Thank you!!

Very much appreciated!!

All comments

Paul Newcome

Try something like this...

=SUMIFS({Range to sum}, {5 Year Capital Plan (LC MC & DC) Range 3}, "MC", {5 Year Capital Plan (LC MC & DC) Range 1}, "Operations")

Gary Gazankas

Thank you your quick response!!

I tried using the formula below (same as the one you provided above) and and get an "Incorrect Argument" error

=SUMIF({5 Year Capital Plan (LC MC & DC) Range 2}, {5 Year Capital Plan (LC MC & DC) Range 3}, "MC", {5 Year Capital Plan (LC MC & DC) Range 1}, "Operations")

Paul Newcome

You need to use a SUMIFS with the S on the end. Not a SUMIF without the S.

Gary Gazankas

Ahhh, that did it!!

Thank you!!

Very much appreciated!!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.