SOLVED

Accounting spreadsheet

Copper Contributor

Hi!

 

I am using an excel sheet to track my transactions, and I'm trying to make it a little less work while inputting things. However, I can't seem to get a function to do the math I need. 

I'm trying to get the following formula to ADD cells C3 and D5 because the cells B3 and B5 say "N". I want it to ignore row 4 completely because B4 is not "N". So the result should be $0.

 

I am grateful for any and all assistance here. :)

 

annzgabob_1-1661547329327.png

 

 

4 Replies
best response confirmed by annzgabob (Copper Contributor)
Solution

how about =SUM(FILTER(C3:D5,B3:B5="N"),0)
if you don't have excel 365 then maybe
=SUMIFS(C3:C5,B3:B5,"N")+SUMIFS(D3:D5,B3:B5,"N")

or

=SUMPRODUCT(C3:D5*(B3:B5="n"))

@annzgabob 

Use

 

=SUMPRODUCT((B3:B5="N")*C3:D5)

AH! You did it! :D Thank you so much @mtarler! I used the 2nd function you mentioned and it worked in Excel and "Excel for the web".

@annzgabob 

Any chance you are using Excel 365? If so

= SUM(FILTER(values, criterion="N"))

= SUM(IF(criterion="N", values))

both work, where 'criterion' is the column with "N" & "B" and values is the other two columns (each 3 rows at present)

1 best response

Accepted Solutions
best response confirmed by annzgabob (Copper Contributor)
Solution

how about =SUM(FILTER(C3:D5,B3:B5="N"),0)
if you don't have excel 365 then maybe
=SUMIFS(C3:C5,B3:B5,"N")+SUMIFS(D3:D5,B3:B5,"N")

or

=SUMPRODUCT(C3:D5*(B3:B5="n"))

View solution in original post