Excel: Sum cells if values in cell1="a" and cell2="b" 2

[mflancour]

I am trying to figure out a formula that will search a column for all cells containg "a" with "b" in the next column of the same row then sum all data where both values are true.

If column1="a" then if column2="b" then sum column3 for all rows matching this criteria.
c1 c2 c3
a b $5
c b $5
a f $5
a b $5
c f $5

result of formula should sum rows:1 and 4 for total of $10

 

[DaleWatson123321]

Excellent example of where a DATABASE formula can be used.

Steps:

1) Assign a range name (for example "data&quot to range A1:C:10000. Allowing "extra" space is appropriate if your database will grow in size.

2) Set up a "criteria" (preferably on a separate sheet) that would include 4 cells. These 4 cells can be placed anywhere on the sheet, but for this example we'll use cells B2:C3, as follows...

In cell B2 and C2, copy the field names ("c1" and "c2&quot

In cell B3, enter "a" (no quotation marks)
In cell C3, enter "b" (no quotation marks)

3) Assign a range name (example "crit&quot to the 4 cells.

4) Method of assigning a range name:
a) Highlight the cell or range
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

5) Enter the following database formula in whatever cell you require it...

=DSUM(data,3,crit)

The database formula consists of 3 parts:
a) reference to the &quot;database&quot;
b) reference to the &quot;offset&quot;, or column from which you want to pull the numbers from (in this case &quot;add&quot.
c) reference to the range containing your &quot;criteria&quot;.

Hope this helps. . If you encounter any problems, I can email you example files that will provide a variety of different examples, including formulas, AND Excel's ability to use the same criteria to extract out those records &quot;behind the formulas&quot;.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Or an array formula:
=SUM((A1:A100=&quot;a&quot*(B1:B100=&quot;b&quot*(C1:C100))
Use CTRL+SHIFT+ENTER to enter this formula - don't use just ENTER

If you need to do this for a lot of different values / combinations, I would suggest Dale's database function method but for a few, the overhead is probably less with array formulae

HTH
~Geoff~

 

[mflancour]

Thanks a lot. Both of these methods worked well. Not sure wich one I'll use yet, but they are both very usful. Oh, and thanks for the ctrl-shift-enter. Why does this need to be done anyway? What does it do? How did you know about it?

 

[xlbo]

All array formulae need to be entered using ctrl + shift + enter - it identifies to excel that you want to evaluate the formula in a different way. Essentially, an array formula will evaluate a true / false condition and return a matrix of 0s and 1s which, when multiplied, result in a 1 or 0 (all true or at least 1 false). This on its own gives you a count. Multiplying by the range values gives the sum
If you want to know more, try this:

http://www.emailoffice.com/excel/arrays-bobumlas.html

HTH
~Geoff~