SUMIF based on two criterias

[buntykins]

Is it possible to do a SUMIF formula based on two criterias? Basically what I have is a sheet with three columns - ID (Column A), Date (Column B) and Amount (Column C)

What I need to find is how much a certain user took on a certain day, so basically add together all the amount entries taken by ID number 2222 on the 28/04/02. Is this possible?

Janie

xx

[ This Message was edited by: buntykins on 2002-04-29 07:29 ]

[Jay Petrulis]

On 2002-04-29 07:28, buntykins wrote:
Is it possible to do a SUMIF formula based on two criterias? Basically what I have is a sheet with three columns - ID (Column A), Date (Column B) and Amount (Column C)

What I need to find is how much a certain user took on a certain day, so basically add together all the amount entries taken by ID number 2222 on the 28/04/02. Is this possible?

Janie

xx

[ This Message was edited by: buntykins on 2002-04-29 07:29 ]

=SUMPRODUCT((A1:A100=2222)*(B1:B100=Date(2002,4,28))*(C1:C100))

should work for you. Adjust your ranges as necessary.

Bye,
Jay

[buntykins]

Hi,

Sort of works, except that the place that the formula is going is a separate sheet and because of this it doesnt seem to work.

Any idea on how I could fix this?

[ This Message was edited by: buntykins on 2002-04-29 07:51 ]

[buntykins]

Maybe I should have mentioned that the things that the SUMPRODUCT is looking for are values in cells. For instance, my formula at the moment looks like this:

=SUMPRODUCT((B1:B60000=X2)*(J1:J60000=DATE(AA2,Z2,Y2))*(E1:E60000))

Help!!!

[Jay Petrulis]

How are the dates formatted? Can you break it down into smaller bits that you can post? Your formula should be working.

[buntykins]

The date is formatted like this: 28/04/02 but I have an =Date and =Month and an =Day formula to break the date down into three cells so that I have 3 separate parts for the SUMPRODUCT formula

Weird!

[Aladin Akyurek]

On 2002-04-29 08:26, buntykins wrote:

The date is formatted like this: 28/04/02 but I have an =Date and =Month and an =Day formula to break the date down into three cells so that I have 3 separate parts for the SUMPRODUCT formula

Weird!

Does it work now? In fact you shouldn't to break up the criterion date.

Is the actual, used range really 60000 big? If not, I think you should be better off using dynamic name ranges.

[Audiojoe]

You could use a frinkin' pivot table I guess?

Audiojoe

[buntykins]

Of course!!!! Nice one Joe, here I am fiddling with formulas when I could just display it there all the time!!

Thanks baby

xxxxxx