Excel Query Help

[krappleby025]

Hi all,

Im trying to run what i believe to be a simple query over two excel spread sheets in the same workbook, but i cannot get it to work.

I have an excel spreadsheet with 5-6000 records, each record has 7-9 columns of data.

In the second sheet im trying to generate a report based on the data, and the query im trying to set up is something like this

Count the number of rows, where (row)column A contains 345 AND (ROW) column D contains "refered".

Now i have tried several different statements to do this, Including IF, and COUNTIF, but i just cannot seem to get it to work for more than one variable.

I have been on at this for a week looking up data and infomration but to no luck

Can someone help

Cheers
Keith

 

[KenWright]

using your spelling of refered

=SUMPRODUCT((Sheet1!A1:A6000=345)*(Sheet1!D16000="refered"))

Assumes nothing else in those cells but the values of 345 and refered and that your data is on Sheet1

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

PS, A pivot table would this for you in a heartbeat.

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------