Sumif but need to skip over cells

[BajaRose]

Hi all!

Happy I found this forum as I'm in a pickle. I'm tracking some finances for my lab and cant figure this out. So I need the values of column c (total reimbursements submitted) to equal column b (total reimbursed) for each individual based on whether columns G4, l4, q4, v4, aa4 say 'Y' or 'N'. If these columns are 'y' then the value in F4, k4,p4,u4, and z4 will be totaled in column c and compared to column c (see below):

=SUMIF(F4,K4,P4,U4,Z4,"Y",G4,L4,Q4,V4,AA4)​

If 'N', the associated value won't be added and the difference can be noted and rectified (and people can get their money!). However, everytime I try the above formula the cell shows up as "#N/A wrong number of arguments" and that 11 arguments are too much. I'm not sure how else to write out the formula as the cells in between are text that I can't delete.

Any help would be appreciated!

 

[Aladin Akyurek]

One of the following...

1.

=SUMPRODUCT(CHOOSE({1,2,3,4,5},G4,I4,Q4,V4,AA4),--(CHOOSE({1,2,3,4,5},F4,K4,P4,U4,Z4)="Y"))

2.

=SUMPRODUCT(G4:AA4,--(MOD(COLUMN(F4:Z4)-COLUMN(F4),5)=0),--(F4:Z4="Y"))

which are equivalent.

 

[BajaRose]

One of the following...

1.

=SUMPRODUCT(CHOOSE({1,2,3,4,5},G4,I4,Q4,V4,AA4),--(CHOOSE({1,2,3,4,5},F4,K4,P4,U4,Z4)="Y"))

2.

=SUMPRODUCT(G4:AA4,--(MOD(COLUMN(F4:Z4)-COLUMN(F4),5)=0),--(F4:Z4="Y"))

which are equivalent.

Thanks! I've tried both but rows that should have a value still shows= up as 0 even with meeting the condition of yes and having a value >0. Maybe I broke excel...

 

[BajaRose]

One of the following...

1.

=SUMPRODUCT(CHOOSE({1,2,3,4,5},G4,I4,Q4,V4,AA4),--(CHOOSE({1,2,3,4,5},F4,K4,P4,U4,Z4)="Y"))

2.

=SUMPRODUCT(G4:AA4,--(MOD(COLUMN(F4:Z4)-COLUMN(F4),5)=0),--(F4:Z4="Y"))

which are equivalent.

Actually figured it out!! Option 1 worked actually just that the value in the cells was classified as something other than numbers. Theres one row in which its not working but I can build from here. Thanks a lot!

 

[Aladin Akyurek]

Actually figured it out!! Option 1 worked actually just that the value in the cells was classified as something other than numbers. Theres one row in which its not working but I can build from here. Thanks a lot!

You are welcome. Actually, both formulas should work as advertised. Note that the second test for and sum every 5th value.