COUNTIFS or SUMPRODUCT

[dunmore83]

Excel 2012
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
4	Date			Site			Pad			Seed			Hectares			Kg Applied			kg/ha
5
6
7	12-Mar-15			Pivot 2			Pad 4			WS II			7.00			150			21.43
8	12-Mar-15			Pivot 2			Pad 4			Bolta			7.00			10			1.43
9	12-Mar-15			Pivot 2			Pad 4			Paradana			7.00			10			1.43
10	12-Mar-15			Pivot 2			Pad 5			WS II			7.00			150			21.43
11	12-Mar-15			Pivot 2			Pad 5			Bolta			7.00			10			1.43
12	12-Mar-15			Pivot 2			Pad 5			Paradana			7.00			10			1.43
13	12-Mar-15			Pivot 2			Pad 6			WS II			7.00			275			39.29
14	12-Mar-15			Pivot 2			Pad 6			Bolta			7.00			14			2.00
15	12-Mar-15			Pivot 2			Pad 6			Paradana			7.00			14			2.00
16	13-Mar-15			Pivot 2			Pad 3			WS II			7.00			275			39.29
17	13-Mar-15			Pivot 2			Pad 3			Bolta			7.00			15			2.14
18	13-Mar-15			Pivot 2			Pad 3			Paradana			7.00			15			2.14
19	13-Mar-15			Pivot 2			Pad 2			WS II			7.00			200			28.57
20	13-Mar-15			Pivot 2			Pad 2			Bolta			7.00			12			1.71
21	13-Mar-15			Pivot 2			Pad 2			Paradana			7.00			12			1.71
22	13-Mar-15			Pivot 2			Pad 1			WS II			7.00			200			28.57
23	13-Mar-15			Pivot 2			Pad 1			Bolta			7.00			12			1.71
24	13-Mar-15			Pivot 2			Pad 1			Paradana			7.00			12			1.71
25	27-Mar-15			Pivot 6			Pad 1-3			Adrenalin			21.00			480			22.86
26	27-Mar-15			Pivot 6			Pad 1-3			Bolta			21.00			30			1.43
27	27-Mar-15			Pivot 6			Pad 1-3			Paradana			21.00			30			1.43
28	27-Mar-15			Pivot 6			Pad 4-6			Adrenalin			21.00			475			22.62
29	27-Mar-15			Pivot 6			Pad 4-6			Bolta			21.00			30			1.43
30	27-Mar-15			Pivot 6			Pad 4-6			Paradana			21.00			30			1.43
31	19-Apr-15			Pivot 2			Pad 4-6			Adrenalin			15.00			375			25.00
32	21-Apr-15			Pivot 3			Pad 1-3			Adrenalin			20.00			220			11.00
33	22-Apr-15			Pivot 3			Pad 1-3			Adrenalin			20.00			225			11.25
34	24-Apr-15			Pivot 7			Pad 4-5			WS II			9.70			50			5.15
35	24-Apr-15			Pivot 7			Pad 4-5			Shogun			9.70			50			5.15
36	27-Apr-15			Pivot 8			North Half			Dargo			20.00			500			25.00
37	27-Apr-15			Pivot 8			North Half			Bolta			20.00			60			3.00
38	28-Apr-15			Pivot 8			South Half			Dargo			20.00			500			25.00
39	28-Apr-15			Pivot 8			South Half			Bolta			20.00			60			3.00
40	28-Apr-15			Pivot 9			Pad 3-6			Dargo			32.00			775			24.22
41	28-Apr-15			Pivot 9			Pad 3-6			Bolta			32.00			102			3.19
42	02-May-15			Pivot 9			Pad 5-6			WS II - Untreated			12.80			300			23.44
43	02-May-15			Pivot 9			Pad 5-6			Paradana			12.80			30			2.34
44	03-May-15			Pivot 9			Pad 6			WS II			3.80			40			10.53
45	03-May-15			Pivot 9			Pad 6			Dargo			3.80			40			10.53
46	03-May-15			Pivot 9			Pad 6			Paradana			3.80			10			2.63
Seed

 

[dunmore83]

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]

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.

 

[dunmore83]

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]

ADVERTISEMENT

Oops should be;

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

 

[Marcelo Branco]

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]

ADVERTISEMENT

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]

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]

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]

Thanks this is perfect!!! Cheers