SUMIF assistance required as well please?

P

Pavavion

New Member
Joined
Jan 10, 2013
Messages
32
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
ABCDE
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
HPQ
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]
 
Sort by date Sort by votes
Pavavion said:
as the whole workbook seems rather clunky and needs other tweaks.
Click to expand...

It's not the spreadsheet that's clunky. It's the tools you have to work with. Ask them to upgrade you to a commadore 64 :) Seriously though, if you have very old equipment and software, you can't except to do too much with it. Good luck.
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
dermie_72 said:
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.
Click to expand...

I NOW HAVE EXCEL 2010 and new pc so the above works PERFECTLY!!!! thakyou so much tweaking the workbook now fingers crossed can really get this showing some nice stats for me now :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Massive Nested IF Statement Help Needed
Replies
1
Views
380
Zutalores
Z
J
  • Question Question
using sumifs function across multiple sheets
Replies
4
Views
583
KRice
K
J
  • Question Question
sumif function with delayed application
Replies
5
Views
322
jbrown021286
J
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
710
MrDB4Excel
MrDB4Excel
O
  • Question Question
Creating formulas with countifs/sumifs/sumproduct
2 3
Replies
23
Views
2K
Marcelo Branco
M

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

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