count number of occurances for each date

[winrz]

Cant get this and its bugging me. Problem - if every time you ate an apple, you create a new row with todays date and a '1' under the APPLE column. Some days you may eat several (each instance has its own row) and some days you dont eat any.

Now to graph this - I have a matrix below this one with set up for every day of the year. What I want to do is look up at the original matrix and total the number of apples eaten on 1/1/03 and place that count in the lower matrix (next to 1/1/03 and under APPLES). Then, I want to do the same for 1/2/03 and place that under the last entry. so on and so forth... The problem I find is that there may be several rows with 1/1/03 - and I cant just count the times I see 1/1/03 for I may have eaten a Banana that day.


any ideas - I am struggling with the lookup...

 

[Aladin Akyurek]

If I understood your "apples" story correctly, you could try a SUMIF or SUMPRODUCT formula.

 

[Brian from Maui]

Cant get this and its bugging me. Problem - if every time you ate an apple, you create a new row with todays date and a '1' under the APPLE column. Some days you may eat several (each instance has its own row) and some days you dont eat any.

Now to graph this - I have a matrix below this one with set up for every day of the year. What I want to do is look up at the original matrix and total the number of apples eaten on 1/1/03 and place that count in the lower matrix (next to 1/1/03 and under APPLES). Then, I want to do the same for 1/2/03 and place that under the last entry. so on and so forth... The problem I find is that there may be several rows with 1/1/03 - and I cant just count the times I see 1/1/03 for I may have eaten a Banana that day.


any ideas - I am struggling with the lookup...

Maybe a Pivot Table or depending on your worksheet, Sumproduct,
as in

=sumproduct((range=a1)*(range=a2))

where a1 houses 1/1/03 and a2 houses apples

 

[Barry Katcher]

Here you go. Formula thanks to Brian from Maui.

Book1
	A	B	C	D
1	1/1/2003	apple
2	1/1/2003	banana
3	1/1/2003	apple
4	1/2/2003	orange
5	1/2/2003	apple
6	1/2/2003	banana
7	1/3/2003	apple
8	1/3/2003	apple
9	1/3/2003	goatcheese
10
11
12	1/1/2003	1/2/2003	1/3/2003
13	apple	apple	goatcheese
14	2	1	1
15
Sheet2

 

[winrz]

Let me revamp my APPLE analogy.
Every time I PURCHASE an apple, I place the price in a new row under APPLE.
Since I have prices - a SUMIF would just total the prices (..i do currently track the totals i paid for apples each day with a sumif) .. but i cant figure out how to count the number of times I bought an apple for a particualr day.

 

[Aladin Akyurek]

Let me revamp my APPLE analogy.
Every time I PURCHASE an apple, I place the price in a new row under APPLE.
Since I have prices - a SUMIF would just total the prices (..i do currently track the totals i paid for apples each day with a sumif) .. but i cant figure out how to count the number of times I bought an apple for a particualr day.

Could you please post 5 to 10 rows of your data?

 

[winrz]

I GOT IT. Thanks guys - and to Barrys formula - I think I have captured the number of instances - I used:
=SUMPRODUCT(($A$8:$A$400=$A858)*(B$8:B$400>0))
where $A$8:$A$400 is the room alotted for the user inputted dates
and $A858 is the date to compare to from below
and B$8:B$400 is the range to look for values greater than $0.

How simple - thanks folks..

 

[Barry Katcher]

...or, another possibility:

Book1
	E	F	G	H	I
1	Date	Apple	Orange	Banana	Goatcheese
2	1/1/2003	1
3	1/1/2003			1
4	1/1/2003	1
5	1/2/2003		1
6	1/2/2003	1
7	1/2/2003			1
8	1/3/2003	1
9	1/3/2003	1
10	1/3/2003				2
11
12	1/1/2003	1/2/2003	1/3/2003
13	apple	apple	apple
14	2	1	2
15	orange	orange	orange
16	0	1	0
17	banana	banana	banana
18	1	1	0
19	goatcheese	goatcheese	goatcheese
20	0	0	2
Sheet2

 

[winrz]

Simple... yet, elegant.....

thanks..