Discussion Locked

Excel: How to sum using two or more if a

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Here's what I am trying to do: (1) I want to look at column A in the file and find all "1"s, (2) then as I find each "1", I want the system to look for a "A" in column B. If both conditions are met, then sum column C. I don't want to use filters because the results need to go on a separate worksheet. Using filters and subtotals in filters will only give me answers on the page that I have the filters. Any ideas??

6 replies

by sankulpune

Hello dbaker,

If you want to sum the column 'C' (say c1:c10 with data in A1:A10 and B1:B10) when even a single pair of 1 & A is found in columns 'A' and 'B', type the following formula in C11:
=IF((SUM(IF(A1:A5=1, IF(B1:B5="A",1,0))))>0, SUM(C1:C10))
You need to convert it into an array formula by pressing CTRL+SHIFT+ENTER instead of just ENTER after typing the formula in the cell.

SanganakSakha,
Simple And Effective Solutions To Small Problems, No QuickFixes.

by dbaker

The formula worked - after I made an array formula out of it by pressing CTRL+SHIFT+ENTER

Thanks

This discussion is closed to new replies.