General discussion

Locked

Excel: How to sum using two or more if a

By dbaker ·
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??

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel: How to sum using two or more if a

by DKlippert In reply to Excel: How to sum using t ...

There's a very good discussion of this problem at:

http://www.cpearson.com/excel/array.htm

(there are no spaces in the URL)

Collapse -

Excel: How to sum using two or more if a

by DKlippert In reply to Excel: How to sum using t ...

Another source is:

http://j-walk.com/ss/excel/tips/tip74.htm

(No spaces)

Collapse -

Excel: How to sum using two or more if a

by dbaker In reply to Excel: How to sum using t ...

Poster rated this answer

Collapse -

Excel: How to sum using two or more if a

by SanKulPune In reply to Excel: How to sum using t ...

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.

Collapse -

Excel: How to sum using two or more if a

by dbaker In reply to Excel: How to sum using t ...

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

Thanks

Collapse -

Excel: How to sum using two or more if a

by dbaker In reply to Excel: How to sum using t ...

This question was closed by the author

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums