Countif Help

[Johnstog]

I have kind of a complex equation I could use assistance with.

In column B:B I have a date "1/01/2011"
In Column E:E I have various units "BI201", "BI205", etc.
In column H:H i have and item code "P", "IFT", "L" etc.

What I need help with is this:
I need to know for all dates in January 2011
How many times did "BI201" match up to the Code "P", "IFT", "L"

Any help you can provide would be greatly appreciated. Thanks ahead of time for any help you can provide.

 

[bolo]

I have kind of a complex equation I could use assistance with.

In column B:B I have a date "1/01/2011"
In Column E:E I have various units "BI201", "BI205", etc.
In column H:H i have and item code "P", "IFT", "L" etc.

What I need help with is this:
I need to know for all dates in January 2011
How many times did "BI201" match up to the Code "P", "IFT", "L"

Any help you can provide would be greatly appreciated. Thanks ahead of time for any help you can provide.

I think you should look up pivot tables for your needs. Look in the help, but the pivot tables should give you all the info you need without the need for formulas.

Regards

Bolo

 

[T. Valko]

I have kind of a complex equation I could use assistance with.

In column B:B I have a date "1/01/2011"
In Column E:E I have various units "BI201", "BI205", etc.
In column H:H i have and item code "P", "IFT", "L" etc.

What I need help with is this:
I need to know for all dates in January 2011
How many times did "BI201" match up to the Code "P", "IFT", "L"

Any help you can provide would be greatly appreciated. Thanks ahead of time for any help you can provide.

What version of Excel are you using?

 

[Johnstog]

2003 Excel

 

[T. Valko]

2003 Excel

Let's assume this is your data:

Book1
	B	E	H
2	1/31/2011	BI208	P
3	3/11/2011	BI210	P
4	1/4/2011	BI201	P
5	1/19/2011	BI203	L
6	3/29/2011	BI207	L
7	2/1/2011	BI204	IFT
8	1/21/2011	BI201	P
9	1/18/2011	BI201	XX
10	3/13/2011	BI208	P
11	1/26/2011	BI201	P
12	4/20/2011	BI203	L
13	1/15/2011	BI201	P
14	2/5/2011	BI201	L
15	12/23/2010	BI207	IFT
16	4/9/2011	BI200	P
17	2/18/2011	BI203	IFT
18	2/2/2011	BI210	L
19	1/17/2011	BI208	L
20	2/18/2011	BI209	XX
Sheet1

Use cells to hold the criteria:

• J2 = the 1st of the month date for the month/year to be counted for. For example, to count for Jan 2011 enter the date 1/1/2011.
• K2 = some unit code like BI201
• L2 = some item code like P

Then:

=SUMPRODUCT(--(B2:B20-DAY(B2:B20)+1=J2),--(E2:E20=K2),--(H2:H20=L2))

Note that with SUMPRODUCT you *can't* use entire columns as range references unless you're using Excel 2007.

 

[Johnstog]

This worked well. Thanks.

I need to calculate by day of week as well. So, if =SUMPRODUCT(--(B2:B20-DAY(B2:B20)+1=J2),....... Is what you use to get the Month & Year, what would I use for Day of Week?

I need to know how many fall on each day of the week.

 

[Aladin Akyurek]

This worked well. Thanks.

I need to calculate by day of week as well. So, if =SUMPRODUCT(--(B2:B20-DAY(B2:B20)+1=J2),....... Is what you use to get the Month & Year, what would I use for Day of Week?

I need to know how many fall on each day of the week.

You mean on Monday, Tuesday, etc. separately without any reference to the month/year of these days?

 

[Johnstog]

Yes, please

 

[Johnstog]

I created a column F:F that takes the date from column B:B and I formatted it to only say the day of the week (Mon, Tues, Wed, etc...). However, Excel still recognizes the data in this column as a day of the week.

I can run the SUMPRODUCT formula, but it gives me a zero value because the data formated in the F:F column is still an actual date.

 

[bolo]

I created a column F:F that takes the date from column B:B and I formatted it to only say the day of the week (Mon, Tues, Wed, etc...). However, Excel still recognizes the data in this column as a day of the week.

I can run the SUMPRODUCT formula, but it gives me a zero value because the data formated in the F:F column is still an actual date.

use the weekday function to convert a date into a number 1 to depending on the day and then use this in the sumproduct.

Regards

Bolo