Excel Formula - Sum in some Column If Cell in The Same Row in Different Column is NOT Blank

0
votes

I am looking for some formula that will sum for me specific column - AP but with few conditions.

I have 2 worksheets that are relevant - "Final" and "Visual". In "Final" WS I have all the employee numbers in column A. One row for each employee. In Visual WS I have few rows for each employee with the employee numbers in column A too.

I need a formula that will sum to Final worksheet just the rows from column AP in "Visual" that are standing in few standards:

  1. The employee Number in that row of column AP in "Visual" worksheet will be the same as in the row in Final sheet - where I want to show the result. It means that the data is of the same specific employee.
  2. The row of the number in column AP in "Visual in column AF is NOT blank.

I tried few versions of SUMIFS and it didn't work, That what was not working:

=SUMIFS(Visual!AP:AP,Visual!A:A,A3,Visual!AF:AF,"<>0") =SUMIFS(Visual!AP:AP,Visual!A:A,A3,OFFSET(Visual!AF:AF,0,10),"<>0")

if it looks like this:

column A | column AF |column AP
6655     |120        |120
6655     |152        |152
6655     |0          |500
6655     |0          |259
2541     |589        |589
2541     |0          |89
3268     |15         |15

In the example I need to sum for employee 6655 just first two rows of AP column

Thanks.

1
excelexcel-formula
Have your tried SUMIFS? – Scott Craner
@ScottCraner yes, I wrote it in my massage – Rafael Osipov
You should post the ones that did not work so we do not retry them – Scott Craner
@ScottCraner edited – Rafael Osipov
searching for non blank is "<>" not "<>0" See my answer below – Scott Craner

1 Answers

1
votes

Assuming you are entering this formula on the "FINAL" worksheet, and the employee ID is in A3. Use the following formula and copy down.

=SUMIFS(Visual!AP:AP,Visual!A:A,A3,Visual!AF:AF,"<>"&"")

Update

I see your sample data and thank you for that. There is a bit of confusion over blank and zero. I edited your example data to include a blank. I also provided three separate formulas. Which one is giving the correct value if any?

poc