Sum a formula result over a range of cells

Hello all,

I have one column that marks attendance, N, (indicated by "1") and a corresponding column that denotes gender, D. I want to calculate the M:F ratio at each event. The aim of this formula is to tally the number of Males (M) present at the event.

This is the formula I currently have

=IF(ISNUMBER(MATCH(1,N12,0)),(COUNTIF(D12,M)),0)

The problem is I want to SUM the result of this calculation for rows 12-25, without retyping the formula over and over again. Any suggestions would be helpful.

Thank you in advance, Allison.

why can't you just copy the formula down? click on the cell with the formula, look at the lower right corner of the selected cell, you will see a little square box, put your mouse over it and drag it down

alternatively you can copy and paste

or copy and paste special formula in to the appropriate cells

p.s. your formula should be =IF(ISNUMBER(MATCH(1,N12,0)),(COUNTIF(D12,"M")),0) (the M should be in " ", otherwise it will give you a 0 as the result)

I am looking for a total attendance, rather than a list of zeros and ones.

Thank you for that catch, on my sheet "M" is actually a cell that I use as a key. The formula works fine, I just want a way to sum the number of "M" in column D, but onl if there is a "1" in column N.

The closest formula I can get is

=SUM(IF(ISNUMBER(MATCH(1,N12,0)),(COUNTIF(D12,M)),0), =IF(ISNUMBER(MATCH(1,N13,0)),(COUNTIF(D13,M)),0), =IF(ISNUMBER(MATCH(1,N14,0)),(COUNTIF(D14,M)),0), =IF(ISNUMBER(MATCH(1,N15,0)),(COUNTIF(D15,M)),0), =IF(ISNUMBER(MATCH(1,N16,0)),(COUNTIF(D16,M)),0), etc)... however I feel like there should be an easier way.

The countifs works!! Thank you so much guys, you have no idea how many different variations of formulas I tried. You are all the best. Allison

The other problem I noticed with your formula is you may be missing the quotations around the M.

=IF(ISNUMBER(MATCH(1,N12,0)),(COUNTIF(D12,"M")),0)

after you copy and paste that into all of your cells. You can do a sum function on all to get a count of all males who were present.

Or alternatively you can use this formula;

=COUNTIFS(D2:D37,"M",N2:N37,1)

The COUNTIFS function was introduced in Excel 2007. It allows you to specify several criteria. In this example D2:D37 is the range of cells to look for a text value of M. And then it examine ranfe N2:N37 for a 1. If both of the criteria are true it adds to a count. It will examine the entire range you specify. You can change the formula easily to point to a different range of cells or the value you are looking for.

If you are looking for the total number of Males in a single formula you can use =SUMIFS(N:N,D:D,"M") assuming attendance (N) is 1 or 0 or you can use =COUNTIFS(N:N,1,D:D,"M") in both cases you can substitute a specific range for the column (i.e. - instead of N:N use N2:N100).

Can you post a copy of your spreadsheet with fake information? It may be easier for us to assist if we understand better.

glad we could help you Allison, this is the place to get excel help!

Try it like this:

=COUNTIFS(N12:N16,1,D12:D16,"M")

from her original post, it sounds like its either 1 or nothing, so i don't think she needs to sumifs(), but i could be wrong