# COUNTIFS or SUMPRODUCT

#### dunmore83

##### Well-known Member

Excel 2012
EFGHIJKLMNOPQRSTUVW
5
6
3627-Apr-15Pivot 8North HalfDargo20.0050025.00
3727-Apr-15Pivot 8North HalfBolta20.00603.00
3828-Apr-15Pivot 8South HalfDargo20.0050025.00
3928-Apr-15Pivot 8South HalfBolta20.00603.00
4202-May-15Pivot 9Pad 5-6WS II - Untreated12.8030023.44
Seed

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### dunmore83

##### Well-known Member
Hi,

I need a formula for the above display of data to satisfy these rules;

- search the table for text in column N
- when a match is found for text in column N then sum those kg's applied in column T but the formula needs to only sum on a month basis

i.e. For March-15 & WSII the formula result would be 1,250

Help greatly appreciated!
-

#### Marcelo Branco

##### MrExcel MVP
Try something like

=SUMIFS(T7:T46,E7:E46,">="&A2,E7:E46,"<="&A3,N7:N46,A4)

where A2, A3 and A4 house the criteria, i.e, respectively, Begin Date, End Date and criteria_text.

A2
01-Mar-15
A3
31-Mar-15
A4
WS II

Hope this helps

M.

#### dunmore83

##### Well-known Member
This works. Thanks for your help!

However can the date portion of the formula be modified so that it references two cells? For example A2 would have 2015 and A3 would have 3 (for March).

I have used this type formula in the past for the above date part;

=(SUMPRODUCT((MONTH(\$G\$7:\$G\$46)=\$A3)*(YEAR(\$G\$7:\$G\$46)=\$A2),(\$T\$7:\$T\$46))/1000)

However I am not sure how to bring in the extra layer of only summing if the text value in column N is satisfied ?

#### dunmore83

##### Well-known Member
Oops should be;

=(SUMPRODUCT((MONTH(\$E\$7:\$E\$46)=\$A3)*(YEAR(\$E\$7:\$E\$46)=\$A2),(\$T\$7:\$T\$46))/1000)

#### Marcelo Branco

##### MrExcel MVP
Try

Assumes: dates in column E; A2=2015; A3=3; A4=WS II

=SUMPRODUCT(--(MONTH(\$E\$7:\$E\$46)=\$A3),--(YEAR(\$E\$7:\$E\$46)=\$A2),--(\$N\$7:\$N\$46=\$A4),\$T\$7:\$T\$46)

M.

#### Cosmic Wizard

##### Board Regular
Try

Assumes: dates in column E; A2=2015; A3=3; A4=WS II

=SUMPRODUCT(--(MONTH(\$E\$7:\$E\$46)=\$A3),--(YEAR(\$E\$7:\$E\$46)=\$A2),--(\$N\$7:\$N\$46=\$A4),\$T\$7:\$T\$46) M.

Thanks Marcelo!! That is a totally awesome formula, just what I was looking for along with dunmore83!!

#### Cosmic Wizard

##### Board Regular
Try

Assumes: dates in column E; A2=2015; A3=3; A4=WS II

=SUMPRODUCT(--(MONTH(\$E\$7:\$E\$46)=\$A3),--(YEAR(\$E\$7:\$E\$46)=\$A2),--(\$N\$7:\$N\$46=\$A4),\$T\$7:\$T\$46)

M.

Quick question, I was using data validation to give me a drop down list for the months, but I want the months to be name such as Jan, Feb, Mar etc.

Yet there seems to be a some error as when the drop down list is used it is not recognized by the formula.

Is this a formula error or a formatting error? Thankyou!

#### Marcelo Branco

##### MrExcel MVP
Are you saying that A3 is a drop down, whose options are text valúes such as: Jan, Feb, Mar....?

If so try
=SUMPRODUCT(--(TEXT(\$E\$7:\$E\$46,"mmm")=\$A3),--(YEAR(\$E\$7:\$E\$46)=\$A2),--(\$N\$7:\$N\$46=\$A4),\$T\$7:\$T\$46)

M.

#### dunmore83

##### Well-known Member
Thanks this is perfect!!! Cheers

Replies
8
Views
167
Replies
7
Views
464
Replies
6
Views
402
Replies
4
Views
319
Replies
6
Views
2K

1,196,021
Messages
6,012,906
Members
441,740
Latest member
Latrs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back