SUMIF assistance required as well please?

[Pavavion]

What I would like to achieve is the following please:

In cell E20: IF H4:H266 =EC & P4:P266=Y, Then total all the values in Q that have met the first 2 criteria.
In cell E21: IF H4:H266 =GS & P4:P266=Y, Then total all the values in Q that have met the first 2 criteria.
And so on for the rest of the sales reps.


Excel 2000

	A	B	C	D	E
Ellie Cox
Georgia Smit
Iain McGowan	-
Internal	-
Kieth Ballamy	-
Mackay
Monnise Sullivan
Mt Isa
Natalie Answerth
Priscilla Giudicatti
Production
Rob Aumend	-
Shane Jurgens
Stacy Hutchison
Wayne MacDonald	-

<tbody>
[TD="align: center"]19[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Account Manager Workload[/TD]
[TD="bgcolor: #C0C0C0, align: center"] Total[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Revenue WIP[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Average TVC Revenue[/TD]
[TD="bgcolor: #C0C0C0, align: center"]ACTUAL TVC REVENUE[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"] $ 750[/TD]
[TD="align: right"] $ 750[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"] $ 1,080[/TD]
[TD="align: right"] $ 270[/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"] $ -[/TD]

[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"] $ -[/TD]

[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"] $ -[/TD]

[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"] $ 300[/TD]
[TD="align: right"] $ 300[/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"] $ 1,000[/TD]
[TD="align: right"] $ 1,000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"] $ 100[/TD]
[TD="align: right"] $ 100[/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"] $ 780[/TD]
[TD="align: right"] $ 390[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"] $ 2,960[/TD]
[TD="align: right"] $ 423[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]1[/TD]
[TD="align: right"] $ 280[/TD]
[TD="align: right"] $ 280[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"] $ -[/TD]

[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"] $ -[/TD]
[TD="align: right"] $ -[/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"] $ 1,900[/TD]
[TD="align: right"] $ 633[/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]0[/TD]
[TD="align: right"] $ -[/TD]

[TD="align: right"][/TD]

</tbody>

Wip Month Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B20[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=eC")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C20[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=EC",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D20[/TH]
[TD="align: left"]=IF(ISERROR(C20/B20),"-",C20/B20)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B21[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=GS")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C21[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=GS",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D21[/TH]
[TD="align: left"]=IF(ISERROR(C21/B21),"-",C21/B21)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B22[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=IM")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C22[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=IM",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D22[/TH]
[TD="align: left"]=IF(ISERROR(C22/B22),"-",C22/B22)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B23[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=int")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C23[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=INT",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D23[/TH]
[TD="align: left"]=IF(ISERROR(C23/B23),"-",C23/B23)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B24[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=kb")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C24[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=KB",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D24[/TH]
[TD="align: left"]=IF(ISERROR(C24/B24),"-",C24/B24)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B25[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=mky")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C25[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=MKY",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D25[/TH]
[TD="align: left"]=IF(ISERROR(C25/B25),"-",C25/B25)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B26[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=MS")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C26[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=MS",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D26[/TH]
[TD="align: left"]=IF(ISERROR(C26/B26),"-",C26/B26)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B27[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=isa")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C27[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=ISA",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D27[/TH]
[TD="align: left"]=IF(ISERROR(C27/B27),"-",C27/B27)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B28[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=na")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C28[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=NA",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D28[/TH]
[TD="align: left"]=IF(ISERROR(C28/B28),"-",C28/B28)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B29[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=Pg")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C29[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=PG",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D29[/TH]
[TD="align: left"]=IF(ISERROR(C29/B29),"-",C29/B29)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B30[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=pro")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C30[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=PRO",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D30[/TH]
[TD="align: left"]=IF(ISERROR(C30/B30),"-",C30/B30)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B31[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=ra")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C31[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=RA",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D31[/TH]
[TD="align: left"]=IF(ISERROR(C31/B31),"-",C31/B31)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B32[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=sj")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C32[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=SJ",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D32[/TH]
[TD="align: left"]=IF(ISERROR(C32/B32),"-",C32/B32)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B33[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=SH")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C33[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=SH",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D33[/TH]
[TD="align: left"]=IF(ISERROR(C33/B33),"-",C33/B33)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]B34[/TH]
[TD="align: left"]=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=wm")[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]C34[/TH]
[TD="align: left"]=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=WM",'WIP NEW TEMPLATE'!$Q$4:$Q$233)[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]D34[/TH]
[TD="align: left"]=IF(ISERROR(C34/B34),"-",C34/B34)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Excel 2000

	H	P	Q
EC
GS
GS
ISA
MKY
MS
PG
SH
SH
SJ
SJ
GS
NA
NA
PG
PG
PG
PG
SH
PG
GS
PG
PRO

<tbody>
[TD="align: center"]3[/TD]
[TD="bgcolor: #000000, align: center"]AM[/TD]
[TD="bgcolor: #000000, align: center"]PAX[/TD]
[TD="bgcolor: #000000, align: center"] Billable[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]10-Jan[/TD]
[TD="align: right"] $ 750[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]Y[/TD]
[TD="align: right"] $ 450[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]Y[/TD]
[TD="align: right"] $ -[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]Y[/TD]
[TD="align: right"] $ 100[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]Y[/TD]
[TD="align: right"] $ 300[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]28-Jan[/TD]
[TD="align: right"] $ 1,000[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]25-Jan[/TD]
[TD="align: right"] $ 500[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]17-Jan[/TD]
[TD="align: right"] $ 450[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]Y[/TD]
[TD="align: right"] $ 450[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]28-Jan[/TD]
[TD="align: right"] $ -[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]28-Dec[/TD]
[TD="align: right"] $ -[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]Y[/TD]
[TD="align: right"] $ 630[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]Y[/TD]
[TD="align: right"] $ 280[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]HOLD[/TD]
[TD="align: right"] $ 500[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]HOLD[/TD]
[TD="align: right"] $ 500[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]HOLD[/TD]
[TD="align: right"] $ 500[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]HOLD[/TD]
[TD="align: right"] $ 500[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]HOLD[/TD]
[TD="align: right"] $ 500[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]HOLD[/TD]
[TD="align: right"] $ 1,000[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]Y[/TD]
[TD="align: right"] $ 180[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]Y[/TD]
[TD="align: right"] $ -[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]20-Jan[/TD]
[TD="align: right"] $ 280[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]17-Jan[/TD]
[TD="align: right"] $ 280[/TD]

</tbody>

WIP NEW TEMPLATE​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P4[/TH]
[TD="align: left"]=IF(S4="HOLD","HOLD",(S4-8))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P9[/TH]
[TD="align: left"]=IF(S9="HOLD","HOLD",(S9-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P10[/TH]
[TD="align: left"]=IF(S10="HOLD","HOLD",(S10-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P11[/TH]
[TD="align: left"]=IF(S11="HOLD","HOLD",(S11-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P13[/TH]
[TD="align: left"]=IF(S13="HOLD","HOLD",(S13-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P14[/TH]
[TD="align: left"]=IF(S14="HOLD","HOLD",(S14-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P17[/TH]
[TD="align: left"]=IF(S17="HOLD","HOLD",(S17-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P18[/TH]
[TD="align: left"]=IF(S18="HOLD","HOLD",(S18-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P19[/TH]
[TD="align: left"]=IF(S19="HOLD","HOLD",(S19-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P20[/TH]
[TD="align: left"]=IF(S20="HOLD","HOLD",(S20-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P21[/TH]
[TD="align: left"]=IF(S21="HOLD","HOLD",(S21-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P22[/TH]
[TD="align: left"]=IF(S22="HOLD","HOLD",(S22-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P25[/TH]
[TD="align: left"]=IF(S25="HOLD","HOLD",(S25-3))[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]P26[/TH]
[TD="align: left"]=IF(S26="HOLD","HOLD",(S26-3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[cyrilbrd]

Hi,
Like this?
=SUMIFS(Q4:Q266,H4:H266,"EC",O4:O266,"Y")
=SUMIFS(Q4:Q266,H4:H266,"GS",O4:O266,"Y")

 

[dermie_72]

Use:

Sumifs(sum range, criteria range1, criteria 1, criteria range2, criteria2)

So youres would looks a little like this (assuming you're in cell E20)

=sumifs(Q:Q,H:H,"EC",P:P,"Y")

if you need to put ranges in, then all the ranges need to equal each other, incluing the sum ranges.

Hope that helps.

 

[Pavavion]

Excel 2000
	A	B	C	D	E
19	Account Manager Workload	Total	Revenue WIP	Average TVC Revenue	ACTUAL TVC REVENUE
20	Ellie Cox	1	$ 750	$ 750	#NAME?
21	Georgia Smit	4	$ 1,080	$ 270
22	Iain McGowan	0	$ -	-
23	Internal	1	$ -	$ -
24	Kieth Ballamy	0	$ -	-
25	Mackay	1	$ 300	$ 300
26	Monnise Sullivan	1	$ 1,000	$ 1,000
27	Mt Isa	1	$ 100	$ 100
28	Natalie Answerth	2	$ 780	$ 390
29	Priscilla Giudicatti	7	$ 2,960	$ 423
30	Production	1	$ 280	$ 280
31	Rob Aumend	0	$ -	-
32	Shane Jurgens	2	$ -	$ -
33	Stacy Hutchison	3	$ 1,900	$ 633
34	Wayne MacDonald	0	$ -	-
Wip Month Summary

Cell Formulas
Range	Formula
B20	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=eC")
B21	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=GS")
B22	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=IM")
B23	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=int")
B24	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=kb")
B25	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=mky")
B26	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=MS")
B27	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=isa")
B28	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=na")
B29	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=Pg")
B30	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=pro")
B31	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=ra")
B32	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=sj")
B33	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=SH")
B34	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=wm")
C20	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=EC",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C21	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=GS",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C22	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=IM",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C23	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=INT",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C24	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=KB",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C25	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=MKY",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C26	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=MS",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C27	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=ISA",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C28	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=NA",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C29	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=PG",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C30	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=PRO",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C31	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=RA",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C32	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=SJ",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C33	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=SH",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
C34	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=WM",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
D20	=IF(ISERROR(C20/B20),"-",C20/B20)
D21	=IF(ISERROR(C21/B21),"-",C21/B21)
D22	=IF(ISERROR(C22/B22),"-",C22/B22)
D23	=IF(ISERROR(C23/B23),"-",C23/B23)
D24	=IF(ISERROR(C24/B24),"-",C24/B24)
D25	=IF(ISERROR(C25/B25),"-",C25/B25)
D26	=IF(ISERROR(C26/B26),"-",C26/B26)
D27	=IF(ISERROR(C27/B27),"-",C27/B27)
D28	=IF(ISERROR(C28/B28),"-",C28/B28)
D29	=IF(ISERROR(C29/B29),"-",C29/B29)
D30	=IF(ISERROR(C30/B30),"-",C30/B30)
D31	=IF(ISERROR(C31/B31),"-",C31/B31)
D32	=IF(ISERROR(C32/B32),"-",C32/B32)
D33	=IF(ISERROR(C33/B33),"-",C33/B33)
D34	=IF(ISERROR(C34/B34),"-",C34/B34)
E20	=SUMIFS('WIP NEW TEMPLATE'!Q4:Q232,'WIP NEW TEMPLATE'!H4:H232,"EC",'WIP NEW TEMPLATE'!P4:P232,"Y")

 

[cyrilbrd]

do you really mean Excel 2000 ?

 

[Pavavion]

Use:

Sumifs(sum range, criteria range1, criteria 1, criteria range2, criteria2)

So youres would looks a little like this (assuming you're in cell E20)

=sumifs(Q:Q,H:H,"EC",P:P,"Y")

if you need to put ranges in, then all the ranges need to equal each other, incluing the sum ranges.

Hope that helps.

Thankyou for the reply I am still getting the below error when using this formula sorry:

Excel 2000
	A	B	C	D	E
19	Account Manager Workload	Total	Revenue WIP	Average TVC Revenue	ACTUAL TVC REVENUE
20	Ellie Cox	1	$ 750	$ 750	#NAME?
Wip Month Summary

Cell Formulas
Range	Formula
B20	=COUNTIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=eC")
C20	=SUMIF('WIP NEW TEMPLATE'!$H$4:$H$233,"=EC",'WIP NEW TEMPLATE'!$Q$4:$Q$233)
D20	=IF(ISERROR(C20/B20),"-",C20/B20)
E20	=SUMIFS('WIP NEW TEMPLATE'!$Q$4:$Q$232,'WIP NEW TEMPLATE'!$H$4:$H$232,"EC",'WIP NEW TEMPLATE'!$P$4:$P$232,"Y")

 

[dermie_72]

You may have to use sumproduct instead, if you're using Excvel 2000, the sumifs function is not recognised.

SUMPRODUCT function - Excel - Office.com

or there are plenty of examples here in MrExcel.com.

 

[dermie_72]

Duplicate! Sorry.

 

[tusharm]

Excel 2000? Really? {grin}

I have no way of testing with 2000 -- there have been 5 releases *after* it!

If 2000 supports PivotTables, that would be an alternative to using formulas. I use PTs very effectively to compute the kind of results you want.

What I would like to achieve is the following please:

In cell E20: IF H4:H266 =EC & P4:P266=Y, Then total all the values in Q that have met the first 2 criteria.
In cell E21: IF H4:H266 =GS & P4:P266=Y, Then total all the values in Q that have met the first 2 criteria.
And so on for the rest of the sales reps.


Excel 2000{snip}

 

[Pavavion]

do you really mean Excel 2000 ?

Unfortunately YES my current pc is running excel 2000. I am putting my case to upper management for replacement of my pc with upgrade of office to 2010. FYI the PC is from 1998............ (no hardware upgrades no idea what the previous production manager was doing tbh......)

Anyway the lowest excel I can see in use would be 2003 so that would be the lowest to work to. (Let me get my upgrade first then see if i can help the rest of the department as well fingers x)


I will have to look into pivot tables perhaps as the whole workbook seems rather clunky and needs other tweaks.