COUNTIF to match criteria values from 2 columns

Hi

Can anyone tell me how I can use the COUNTIF (or any other function) that can be used to check for 2 criteria in multiple columns?

For example in column X I need to search for Successful and in column G also check for M so that I can get the gender split.

I have tried putting an AND logical function in there but it just returns the value of TRUE rather that a number which is what I needed.

Thanks in advance for any help offered.

Pre XL2007 you are looking at using SUMPRODUCT

=SUMPRODUCT(--($G$1:$G$1000="M"),--($X$1:$X$1000="Successful"))

note with SUMPRODUCT it is imperative to keep range sizes to a minimum given it is a "hungry" function in terms of performance.

Have you thought of perhaps using a pivot table ?

Hi DonkeyOte,

Thank you so much for your prompt reply. I did use a pivot table in the end, I just wanted to know if this could be done by a formula also and I thank you for providing me the function to do this. I have never used SUMPRODUCT before.

Also my source data was exstracted from a db and there are several columns that contain blank cells and I was worried because I have read that there should be no blank rows or columns when using a Pivot Table report.

What are the implications of blank cells?

A PT can't be set-up if the range contains blank headers.

Nulls are generally more troublesome than Blanks, a Null is a text string so this can cause issues if interspersed amongst other numerical values in fields which you would otherwise like to "Group" etc in the PT.
(and in reality the issue of Nulls/Blanks applies to more than just PTs...)

Originally Posted by DonkeyOte

A PT can't be set-up if the range contains blank headers.

Nulls are generally more troublesome than Blanks, a Null is a text string so this can cause issues if interspersed amongst other numerical values in fields which you would otherwise like to "Group" etc in the PT.
(and in reality the issue of Nulls/Blanks applies to more than just PTs...)

Thanks for this addional information and for your help, I really appreciate it the SUMPRODUCT function worked extremely well and returned the same value as the Pivot Table so I was able to confirm that my figures were correct.