Formula for summing based on 2 criteria?

[Daniellel]

Hi,

What formula can I use to find out what the sum of column "D" is if Column "A" = Jan and Column "B" = Monday???

I have a list that i need to report the number of vacancies (Column D) were submitted on a Monday (Column B) in January (Column A).

I then need to replicate this sum for all days and months...

Not sure what I need, I thought something like...

=sumif(A1:A999="Jan", B1:B999="Mon",D1:D999)

But this obviously does not work. I have Excel 2003 if that helps???

PLEASE HELP!

 

[VoG]

Try

=SUMPRODUCT(--(A1:A999="Jan"),--(B1:B999="Mon"),D1:D999)

 

[Daniellel]

Try

=SUMPRODUCT(--(A1:A999="Jan"),--(B1:B999="Mon"),D1:D999)

Thanks, this returns a "0" when it should say 137?

 

[VoG]

Should it be Mon or Monday?

 

[Yahya]

if you are using Excel 2007 or 2010 you can use this formula too
=SUMIFS(D1:D999,A1:A999,"Jan",B1:B999,"Mon")

 

[VoG]

Small example

Excel Workbook
	A	B	C	D	E
1	Jan	Mon		1	5
2	Feb	Tue		2
3	Mar	Mon		3
4	Jan	Mon		4
Sheet2

 

[Daniellel]

Should it be Mon or Monday?

Mon - Is it making a difference that the data i am trying to sum is on sheet2 and i am creating this sum on sheet1? I have ensured that i entered Sheet2! before each arguement.

 

[Yahya]

Small example

Excel Workbook
	A	B	C	D	E
1	Jan	Mon	*	1	5
2	Feb	Tue	*	2	*
3	Mar	Mon	*	3	*
4	Jan	Mon	*	4	*
Sheet2

peter
may be his data date formatted as month name and days

 

[Daniellel]

if you are using Excel 2007 or 2010 you can use this formula too
=SUMIFS(D1:D999,A1:A999,"Jan",B1:B999,"Mon")

I only have 2003 but thanks...

 

[VoG]

peter
may be his data date formatted as month name and days

Ah, if they are dates formatted to display as day and month perhaps

=SUMPRODUCT(--(MONTH(A1:A999)=1),--(DAY(B1:B99)=2),D1:D999)