SUMPRODUCT is not returning required value

[Sean15]

Hello:

I have a pivot table with Jan...Dec in A6 to A13 and Sum of Amounts in D6 to D13.

I want the add sum of amount for Jan, Feb and Mar in I9. The formula below is returning zero.

=SUMPRODUCT(($A$6:$A$13="Jan")*($A$6:$A$13="Feb")*($A$6:$A$13="Mar")*($D$6:$D$13))

Could you help me fix formula?

Thank you

Sean

 

[jtakw]

Hi,

If those are Real Dates in Column A, try this:

Book1
	A	D	I
6	Jan	1
7	Feb	2
8	Mar	3
9	Apr	4	6
10	May	5
11	Jun	6
12	Jul	7
13	Aug	8
Sheet89

Cell Formulas
Range	Formula
I9	=SUMPRODUCT((MONTH($A$6:$A$13)={1,2,3})*$D$6:$D$13)

 

[Sean15]

Hi:

No dates in column A. just month name displayed in pivot table as Jan, Feb, Mar...Dec

Your formula is returning #value !


Regards,

Sean

 

[jtakw]

Ok, so Column A is TEXT, then do this:

Book1
	A	D	I
6	Jan	1
7	Feb	2
8	Mar	3
9	Apr	4	6
10	Jun	5
11	Jul	6
12	Aug	7
13	Sep	8
Sheet89

Cell Formulas
Range	Formula
I9	=SUMPRODUCT(($A$6:$A$13={"Jan","Feb","Mar"})*$D$6:$D$13)

 

[Sean15]

Excellent, thank you so much.


Regards,

Sean

 

[jtakw]

You're welcome.

 

[MARZIOTULLIO]

I9=sumproduct(sumif($a$6:$a$13,$a$6:$a$8,$d$6:$d$13))

 

[Sean15]

Ok, so Column A is TEXT, then do this:

Book1
	A	D	I
6	Jan	1
7	Feb	2
8	Mar	3
9	Apr	4	6
10	Jun	5
11	Jul	6
12	Aug	7
13	Sep	8
Sheet89

Cell Formulas
Range	Formula
I9	=SUMPRODUCT(($A$6:$A$13={"Jan","Feb","Mar"})*$D$6:$D$13)

Hi:

Sorry to bring this up again, if there are dates in column A, like:

01/10/18
02/05/18
03/01/18
03/02/17

and I want to add amounts for Jan-Mar 18.

Could you amend formula?

Thanks,

Sean

 

[Joe4]

Assuming that your entries are still text and not valid dates, try:

=SUMPRODUCT((LEFT($A$6:$A$13,2)={"01","02","03"})*(RIGHT($A$6:$A$13,2)="18")*$D$6:$D$13)

If they are actually valid date entries, you can use this instead:

=SUMPRODUCT((MONTH($A$6:$A$13)={1,2,3})*(YEAR($A$6:$A$13)=2018)*$D$6:$D$13)

 

[Sean15]

@Joe4 - thank you very much.


Regards,

Sean