Help Needed in Complex SumIF function

**ulala2** · 03-16-2018, 03:05 AM

Sample.png

I have a data sheet as per enclosed photographs.
In the data:

Column E = Unit No.
Column X = Unit Sign Off Date

I would like to find a formula to calculate "Total Units Sign Off" with the following condition:

1) There must have a unit sign off date enter at Column X;
2) If multiple entries for the same unit at Column E, all respective entry row must have a unit sign off date at Column X;

For example:
E3 cell is unit A-03-1, X3 cell is empty
E4 cell is unit A-03-1, X4 cell is empty
E5 cell is unit A-03-1, X5 cell dated 9/8/2017

So in this case, Unit A-03-1 will not be count due to have two entries (E3 & E4) without date at Column X.

Another example:
E11 & E12 cell is unit A-03-5 and both also have date (21/7/17) at X11 & X12 cell. So unit A-03-5 will count as 1.

Based on the sample table provided, the result should be return as 3. Counted units will be A-03-5 (E11 & E12, X11 & X12), A-03-6 (E13 & X13), and A-03-8 (E15 & X15).

Please Help on write to formula for that effect.
Thank you.

**samba_ravi** · 03-16-2018, 03:11 AM

may be like this
=COUNTIFS(E3:E21,"A-03-1",X3:X21,"<>")

**ulala2** · 03-16-2018, 06:29 AM

Originally Posted by nflsales

may be like this
=COUNTIFS(E3:E21,"A-03-1",X3:X21,"<>")

Thanks for the prompt reply.
However this only will count anything related to unit A-03-1.
MY actual date have 4000 rows, this formula unable to count all the entry for all units.

**samba_ravi** · 03-16-2018, 06:36 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**JohnTopley** · 03-16-2018, 06:47 AM

With helper in AC3

=IF(E3=E2,"",IF(COUNTIFS($E$3:$E$21,$E3)=COUNTIFS($E$3:$E$21,$E3,$X$3:$X$21,"<>"),1,""))

Copy down

SUM column to give result.

**ulala2** · 03-19-2018, 12:04 AM

Originally Posted by JohnTopley

With helper in AC3

=IF(E3=E2,"",IF(COUNTIFS($E$3:$E$21,$E3)=COUNTIFS($E$3:$E$21,$E3,$X$3:$X$21,"<>"),1,""))

Copy down

SUM column to give result.

Dear JohnTopley,

Thanks for the help, with helper column, it works.

However, appreciate your further explanation on the provided formula.

**JohnTopley** · 03-19-2018, 04:33 AM

=IF(E3=E2,"",IF(COUNTIFS($E$3:$E$21,$E3)=COUNTIFS($E$3:$E$21,$E3,$X$3:$X$21,"<>"),1,""))

=IF(E3=E2,"" simply checks if it is the first entry for a Unit Number: if YES i.e E3<>E2 it will ouput the result of the next part of the formula

COUNTIFS($E$3:$E$21,$E3) counts the number of occurrences of a given Unit Number

COUNTIFS($E$3:$E$21,$E3,$X$3:$X$21,"<>") counts the number of Unit numbers with "Sign-offs"

IF both counts are equal i.e. all Units signed off, then result is !: otherwise blank.

**ulala2** · 03-20-2018, 06:27 AM

Originally Posted by JohnTopley

=IF(E3=E2,"",IF(COUNTIFS($E$3:$E$21,$E3)=COUNTIFS($E$3:$E$21,$E3,$X$3:$X$21,"<>"),1,""))

=IF(E3=E2,"" simply checks if it is the first entry for a Unit Number: if YES i.e E3<>E2 it will ouput the result of the next part of the formula

COUNTIFS($E$3:$E$21,$E3) counts the number of occurrences of a given Unit Number

COUNTIFS($E$3:$E$21,$E3,$X$3:$X$21,"<>") counts the number of Unit numbers with "Sign-offs"

IF both counts are equal i.e. all Units signed off, then result is !: otherwise blank.

Thanks for the detail explanation

Help Needed in Complex SumIF function

LinkBack

Thread Tools

Rate This Thread

Display

Help Needed in Complex SumIF function

Re: Help Needed in Complex SumIF function

Re: Help Needed in Complex SumIF function

Re: Help Needed in Complex SumIF function

Re: Help Needed in Complex SumIF function

Re: Help Needed in Complex SumIF function

Re: Help Needed in Complex SumIF function

Re: Help Needed in Complex SumIF function

Thread Information

Users Browsing this Thread

Similar Threads

complex SUMIF function...

[SOLVED] Complex Index/Match Function Needed

[SOLVED] Foruma needed to integrate indirect and sumif function

Complex IF function is needed for the following, however gives !#VALUE

Complex lookup extract and concate function needed

SUMIF function help needed Got first now have second question

somewhat complex function needed

Bookmarks

Bookmarks

Posting Permissions