Hi
I have a large database like below. I need to pull data based on catagory,
subject and date. Example would be looking for catagory1, subject2 data for
jan and get 29 or subject1 data for feb and get 7.I set up as database and
pull info for year using this formula =DSUM(Database,C$79,$B$99:$D$100) but
can not get month to work

Date Subject1 subject2 subject3
1-Jan 10 15 20
2-Jan 6 14 3
""
""
1-Feb 7 5 3

Thanks
Mike

Assuming that the dates in Column A are real XL Dates not text try:

=SUMPRODUCT((A2:A310<"")*(MONTH(A2:A310)=1)*(C2:C 310))

Adjusted to suit your own ranges.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"MikeJ" wrote in message
...
Hi
I have a large database like below. I need to pull data based on catagory,
subject and date. Example would be looking for catagory1, subject2 data
for
jan and get 29 or subject1 data for feb and get 7.I set up as database and
pull info for year using this formula =DSUM(Database,C$79,$B$99:$D$100)
but
can not get month to work

Date Subject1 subject2 subject3
1-Jan 10 15 20
2-Jan 6 14 3
""
""
1-Feb 7 5 3

Thanks
Mike

Sandy, can this be done using Sum(Index, Match) entered as a CSE?
Thanks,
Jim

"Sandy Mann" wrote:

Assuming that the dates in Column A are real XL Dates not text try:

=SUMPRODUCT((A2:A310<"")*(MONTH(A2:A310)=1)*(C2:C 310))

Adjusted to suit your own ranges.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"MikeJ" wrote in message
...
Hi
I have a large database like below. I need to pull data based on catagory,
subject and date. Example would be looking for catagory1, subject2 data
for
jan and get 29 or subject1 data for feb and get 7.I set up as database and
pull info for year using this formula =DSUM(Database,C$79,$B$99:$D$100)
but
can not get month to work

Date Subject1 subject2 subject3
1-Jan 10 15 20
2-Jan 6 14 3
""
""
1-Feb 7 5 3

Thanks
Mike

"Jim May" wrote in message
...
Sandy, can this be done using Sum(Index, Match) entered as a CSE?

I can't but perhaps Biff or some of the other clever pople around here can.
INDEX() seems to only return one value and when you try to give it an array
as its second argument it returns the last matching value.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

Thanks for the additional input (I sort's know what you are saying here).
Jim

"Sandy Mann" wrote:

"Jim May" wrote in message
...
Sandy, can this be done using Sum(Index, Match) entered as a CSE?

I can't but perhaps Biff or some of the other clever pople around here can.
INDEX() seems to only return one value and when you try to give it an array
as its second argument it returns the last matching value.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

It seems that I am going cross-eyed, my test was with 15 in C2 and 14 in C3
the array entered formula:

=SUM(INDEX(C2:C3,{1,2}))

returned 15 and I mistook this for the last value not the first.
Highlighting just the INDEX(C2:C3,{1,2}) and pressing F9 to calculate that
part of the formula calculates only 15 not an array {14,15} so it seems that
INDEX() will not accept an array for its second argument.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Jim May" wrote in message
...
Thanks for the additional input (I sort's know what you are saying here).
Jim

"Sandy Mann" wrote:

"Jim May" wrote in message
...
Sandy, can this be done using Sum(Index, Match) entered as a CSE?

I can't but perhaps Biff or some of the other clever pople around here
can.
INDEX() seems to only return one value and when you try to give it an
array
as its second argument it returns the last matching value.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk