CODEWITHSUNDEEP
excelexcel-formula
Zax Ftw
Zax Ftw

Reputation: 103

Sum every second row only if cell below isn't empty?

I'm trying to write an excel formula to sum every second row in given column but only if the cell below/above is not empty.

My excel table is as follows:

      A           B         C
1   Group1      First       3
2               Second      9
3   Group2      First       -
4               Second      8
5   Group3      First       4
6               Second      -

7   Sum         Firsts      <formula1 - should ignore Group3 because its Second cell is empty>   
8               Seconds     <formula2 - should ignore Group2 because its First cell is empty>

Upvotes: 0

Views: 64

Answers (1)

basic
basic

Reputation: 11968

You can use SUMPRODUCT function for firsts:

=SUMPRODUCT((B9=$B$1:$B$7)*($C$1:$C$7)*(B10=$B$2:$B$8)*($C$2:$C$8<>""))

and for seconds:

=SUMPRODUCT((B9=$B$1:$B$7)*($C$1:$C$7<>"")*(B10=$B$2:$B$8)*($C$2:$C$8))

enter image description here

Upvotes: 2

Related Questions