Need help with formula.

[Joe]

Hello,

I am trying to find a formula that will count selected information.
For instance, I have a spreadsheet that has multiple data with many
different columns. However, I am only interested at counting all the
transactions that took place on 10/30/06, West Region, O, Jane placed
on this date.

A B C D
Date Region Descripion Name
10/28/06 North Q Frank
10/28/06 West O Jane
10/29/06 East O Bill
10/29/06 East O Jane
10/29/06 West Q Jane
10/30/06 West O Jane


The total should equal 1.

Any guidance is truly appreciated.

Regards,
Joe

 

[Ken Puls]

Hi there,

I placed your data (excluding headers) in A38. I then put your
criteria in A11 (10/30/06, West Region, O, Jane)

Then put this in a cell somewhere on the sheet:
=SUMPRODUCT(--(A3:A8=A1),--(B3:B8=B1),--(C3:C8=C1),--(D38=D1))

You'll get a count of 1 with the data you gave. If you insert another
line in the middle and copy the last line in there, you'll increase to 2.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Guest]

This can easily be managed with a pivot table. Put the date, region,
description and name on the page portion of the table, put the name field on
the data field and selct count on field settings.
Gary