countif with multiple criteria

[msnook]

I can not seem to figure out my dilema so I thought I would ask for assistance. I have a large spreadsheet in multiple columns and rows. I need to be able to count the number of 1 item as it depends on another item.

for example
Error code Date
1 Jan, 10
1 Feb, 10
2 Jan, 09
3 Jul, 09
1 Jan, 10
3 Mar, 09
2 Apr, 09
43 Jan, 10
2 Dec, 09
24 Feb, 10

The formula needs to count the number of error code 1's that occured in Jan, 10 and the error code 3's that happened in Jul, 09, and etc for the whole sheet. I can't rearrange the spreadsheet and I am trying not to use macro's to keep the sheet easier to look at/use. Any help will be greatly appriciated.

 

[mscallisto]

There are many ways but maybe you should introduce yourself to pivot tables:

http://www.cpearson.com/excel/pivots.htm

sam

 

[SkipVought]

hi,

Jan, 10 is not a date in Excel. It is just a useless string, if you really need a date for collating, grouping or calculations.

faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[msnook]

mscallisto - I know pivot tables, my employer who doesn't needs the info to use so I was trying to make it as easy as I could for him to manipulate the data outside of using a pivot table.

Skip - The Jan, 10 date is a text file that I set up based on a submission date so that I could group items by month.

I have tried to use various methods to count but end up with a #NUM error or 0 or something else.

 

[SkipVought]

"...text file that I set up based on a submission date so that I could group items by month."

WHY? ALWAYS use REAL dates. Real dates can be summarized by days, weeks, months, quarters, years, decades......

Your text "date" is virtually useless.

Change you "date" heading to something else like "Mon"

Using named ranges and assuming that Jan, 10 is in Z1...
[tt]
=COUNTIF(Mon,Z1)
[/tt]
When I do it using your posted table, I get 3.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Whilst I agree with Skip on the value of keeping your dates "real", the solution to your problem as you have asked it is:

=SUMPRODUCT((A2:A100=1)*(B2:B100="Jan, 10"))

Where error codes are in A2:A100 and "Dates" are in B2:B100

Bear in mind though that your initiative to "make it easier" may well backfire if you need to keep adding new formulae for new error codes and dates - a pivot table would actually make this process easier - with a little tuition ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Duh! I'm pretty dense!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

It must be getting on into the evening there Skip - maybe a bourbon will help!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

its been a long day!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[msnook]

I really appreciate everyone's insight and assistance with this.

Please let me know if the code I am using has problems or could be simplified.

=SUMPRODUCT((Sheet2!D25000=M9)*(Sheet2!N2:N5000=L2))

My spreadsheet is on Sheet2
D25000 is 'Requester Code'
M9 is requester I am counting
N2:N5000 is 'Date Tracker'
L2 is date I am counting.

I need to count the number of entries made by a certain requester on a certain day.

It is weird that I swear I tried this code yesterday and it didn't work but appears to be working fine today.

Sometimes excel makes me think I am loosing my mind.

 

[xlbo]

Yeh - that looks about right. Not a lot you can do to shorten without using a pivot table which does all this stuff automatically

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question