COUNTIFS or SUMPRODUCT

dunmore83

Well-known Member
Joined
Aug 24, 2011
Messages
540

Excel 2012
EFGHIJKLMNOPQRSTUVW
4DateSitePadSeedHectaresKg Appliedkg/ha
5
6
712-Mar-15Pivot 2Pad 4WS II7.0015021.43
812-Mar-15Pivot 2Pad 4Bolta7.00101.43
912-Mar-15Pivot 2Pad 4Paradana7.00101.43
1012-Mar-15Pivot 2Pad 5WS II7.0015021.43
1112-Mar-15Pivot 2Pad 5Bolta7.00101.43
1212-Mar-15Pivot 2Pad 5Paradana7.00101.43
1312-Mar-15Pivot 2Pad 6WS II7.0027539.29
1412-Mar-15Pivot 2Pad 6Bolta7.00142.00
1512-Mar-15Pivot 2Pad 6Paradana7.00142.00
1613-Mar-15Pivot 2Pad 3WS II7.0027539.29
1713-Mar-15Pivot 2Pad 3Bolta7.00152.14
1813-Mar-15Pivot 2Pad 3Paradana7.00152.14
1913-Mar-15Pivot 2Pad 2WS II7.0020028.57
2013-Mar-15Pivot 2Pad 2Bolta7.00121.71
2113-Mar-15Pivot 2Pad 2Paradana7.00121.71
2213-Mar-15Pivot 2Pad 1WS II7.0020028.57
2313-Mar-15Pivot 2Pad 1Bolta7.00121.71
2413-Mar-15Pivot 2Pad 1Paradana7.00121.71
2527-Mar-15Pivot 6Pad 1-3Adrenalin21.0048022.86
2627-Mar-15Pivot 6Pad 1-3Bolta21.00301.43
2727-Mar-15Pivot 6Pad 1-3Paradana21.00301.43
2827-Mar-15Pivot 6Pad 4-6Adrenalin21.0047522.62
2927-Mar-15Pivot 6Pad 4-6Bolta21.00301.43
3027-Mar-15Pivot 6Pad 4-6Paradana21.00301.43
3119-Apr-15Pivot 2Pad 4-6Adrenalin15.0037525.00
3221-Apr-15Pivot 3Pad 1-3Adrenalin20.0022011.00
3322-Apr-15Pivot 3Pad 1-3Adrenalin20.0022511.25
3424-Apr-15Pivot 7Pad 4-5WS II9.70505.15
3524-Apr-15Pivot 7Pad 4-5Shogun9.70505.15
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
4028-Apr-15Pivot 9Pad 3-6Dargo32.0077524.22
4128-Apr-15Pivot 9Pad 3-6Bolta32.001023.19
4202-May-15Pivot 9Pad 5-6WS II - Untreated12.8030023.44
4302-May-15Pivot 9Pad 5-6Paradana12.80302.34
4403-May-15Pivot 9Pad 6WS II3.804010.53
4503-May-15Pivot 9Pad 6Dargo3.804010.53
4603-May-15Pivot 9Pad 6Paradana3.80102.63
Seed
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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!
-
 
Upvote 0
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.

Using your example
A2
01-Mar-15
A3
31-Mar-15
A4
WS II

Hope this helps

M.
 
Upvote 0
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 ?
 
Upvote 0
Oops should be;

=(SUMPRODUCT((MONTH($E$7:$E$46)=$A3)*(YEAR($E$7:$E$46)=$A2),($T$7:$T$46))/1000)
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top