Sum cells based on a condition

Topic

New Reply

Hi,

I am trying to sum a series of cells that match a certain condition horizontally

eg

A1 = 2
B1 = “Y”
C1 = 3
D1 =”Y”
E1 = 4
F1 = “”
G1 = 1
H1 = “Y”

So AA1= the total of A1, C1, E1 and G1 if B1, D1, F1 and H1 = “Y”

I also then need the reverse to that if not = “Y”.

I can do this with a complex if statement but the range could be quite large, is there a SUMIF formula that would do this, I have looked at SUMIF, SUMIFS and SUMPRODUCT which do not quite work.

Any advise.

Regards

Mike

Reply | Quote

Replies

Try this:
=SUMPRODUCT(–(B1:H1=”Y”),A1:G1)

Reply | Quote

=SUMIF(B1:H1,”Y”,A1:G1)
and:
=SUMIF(B1:H1,”Y”,A1:G1)

Reply | Quote