Excel SUMPRODUCT help please :)

_Andy_

Good morning people

I have an Excel spreadsheet which I'm adding a simple dashboard/summary sheet to.
To do so I need to calculate (amongst other things) the number of people taking part in a programme that are from a particular region.
The data is imported from Access and is TRUE/FALSE data.

I need to reference different columns to 'say'

How many 'RegionX' people (a true/false column) are still in the programme (another true/false column). (i.e. give me the total number of people that match both criteria)

I'm pretty sure SUMPRODUCT would do this but I have minimal experience of using the function.
Tried playing around with nested IFs etc but got befuddled.

Any help is appreciated
Cheers

PhilCo_2

In below A:A is the region column, =region is what to find. B:B is True/False column. Depending on how field is set you might need to put "" around it.

=SUMPRODUCT((A:A=region)*(B:B=TRUE))

LeifGR

Which version of Excel are you using?

If you have the function COUNTIFS (I do in Excel 2007), then you can do as follows:

Assuming your data is in rows 2 through 4, that you have three regions in columns A through C and that the "in/out" column is D, your formula would be

=COUNTIFS(A2: A4,TRUE,$D2:$D4,TRUE) (remove the space after A2:, it was turning into a smilie and I don't know how to get around that)

PhilCo_2

If there are 2 True/False columns just add another criteria:

=SUMPRODUCT((A:A=region)*(B:B=TRUE)*(C:C=TRUE))

_Andy_

Thanks guys, I'll have a play this afternoon.
Forgot to say, it's 03 so can't use COUNTIFS.
Cheers

LeifGR

If you don't have COUNTIFS, then you could use what is called an array formula. To enter this, you need to type the formula and then press CTRL+SHIFT+ENTER.

With the same assumptions as in my previous post, your formula would be:

=SUM(A2: A4*$D2:$D4) (without the space)

BUT after you have typed that and pressed CTRL+SHIFT+ENTER to commit it into the sheet, it will look like this:

{=SUM(A2: A4*$D2:$D4)}

An example here: http://www.megaupload.com/?d=G2JGS3QQ

This can then be copied across to all your other region column summary cells.

_Andy_

Must be doing something wrong, trying the SUMPRODUCT..

If I enter

=SUMPRODUCT(('Mentor raw data'!C:C=FALSE)*(Mentor raw data'!R:R=TRUE))

I get 'the formula you've entered contains an error..''?

Thanks again

_Andy_

=SUMPRODUCT(('Mentor raw data'!C:C="FALSE")*'Mentor raw data'!R:R="TRUE")

is giving a #NUM! in the cell

_Andy_

Ah guessing I can't reference the whole column,

=SUMPRODUCT(('Mentor raw data'!B1:B250=TRUE)*('Mentor raw data'!R1:R250=TRUE))

Seems to work ok

PhilCo_2

Probably, 2007 lets you just reference the whole column.

_Andy_

Cool, working fine now. Thanks all for help, much appreciated.