IF Statements

Hi, I am trying to create a nested IF AND statement.

I have a grid sheet that has an Approved column (Checkbox), Date column (Date), Name (Text/Number) column and Leave requested column (Text/Number)

On a new sheet I am trying to write an If statement that deals with these conditions.

I would like to sum the numbers for Leave requested for each person when Approved column is ticked and Date is between 01/01/21 and 31/01/21. So far I have this:

=IF({LEAVE FORM INFO 2021 Range 1} = 0, "", IF(AND({LEAVE FORM INFO 2021 Range 2} > 1 / 1 / 21, {LEAVE FORM INFO 2021 Range 2} < 31 / 1 / 21), COUNTIF({LEAVE FORM INFO 2021 Range 3}, {LEAVE FORM INFO 2021 Range 4} = [Column1]@row), ""))

Breakdown:

=IF({LEAVE FORM INFO 2021 Range 1} = 0, "", Is for if Approved not ticked return blank, if ticked then next conditions:

IF(AND({LEAVE FORM INFO 2021 Range 2} > 1 / 1 / 21, {LEAVE FORM INFO 2021 Range 2} < 31 / 1 / 21),

To find only between 01/01/21 - 31/01/21 and if so then:

COUNTIF({LEAVE FORM INFO 2021 Range 3}, {LEAVE FORM INFO 2021 Range 4} = [Column1]@row), ""))

Count up Leave requested column that matches conditions if Name column = Name in sheet Otherwise return blank.

Currently it is returning an invalid argument, Would appreciate any advise!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Try something like this...

=COUNTIFS({Checkbox Range}, @cell = 0, {Name Range}, [Column1]@row, {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021))

Jordan Norris

Hi Paul,

Thank you for your quick response. I am currently trying this method out and have a couple issues. The formula seems to work to meet the conditions stated but this will now just count up how many times the conditions are met? For example: Every time Approved = Checked, name = name stated & dates are satisfied then this +1 to the count.

I am looking for it to count each cell in the "Leave requested" column that meets conditions. So there could be x3 requests that meet all above conditions and would return for example:

Leave requested that meets conditions:

Line 3 = 4hrs,

Line 10 = 5hrs,

Line 22 = 2hrs

the sum(4+5+2) = 11. Is this possible?

Jordan Norris

Hi Paul, I have tried this:

=SUMIFS({LEAVE FORM INFO 2021 Range 3}, {LEAVE FORM INFO 2021 Range 1}, {LEAVE FORM INFO 2021 Range 1} = 1, {LEAVE FORM INFO 2021 Range 4}, {LEAVE FORM INFO 2021 Range 4} = [Column1]@row)

It seems to be working well, yet I still need to add in the conditions only to sum if Date is between 01/01/21 and 31/01/21

How would I add this in to the above formula?

Paul Newcome

Here you go:

=SUMIFS({Range To Sum}, {Checkbox Range}, @cell = 0, {Name Range}, [Column1]@row, {Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021))

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.