Formula not working to show total sales per day, #VALUE! Can't find the issue (Ignore Conditionals) I3 works fine, J3&J4 are what I have tried.

C

CorbenW01

New Member
Joined
Jul 16, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Testsheet.xlsx
BCDEFGHIJ
2DateJob ReferenceInitial Quote TotalW/LFinal Invoice Total# of Leads per dateSales Per Date
31/05/202400
42/05/2024Wang$3,964.31W$3,964.312/05/20241#VALUE!
510/05/2024Pethig$3,790.40L3/05/202400
611/05/2024Wylde$5,721.31W$5,304.924/05/202400
713/05/2024Young$1,185.00W$1,185.005/05/202400
814/05/2024O'brien$3,255.53W$3,255.536/05/202400
915/05/2024Parker$1,273.15W$1,273.157/05/202400
1016/05/2024Theobald$9,880.00W$8,707.698/05/202400
1117/05/2024Rodwell$3,975.849/05/202400
Sheet1
Cell Formulas
RangeFormula
I3:I11I3=COUNTIF(B:B,H3)
J3,J5:J11J3=COUNTIFS($B$4:$B$100,"="&H3,$B$4:$B$100,">"&H3,$E$4:$E$100,L3)
J4J4=SUM(COUNTIFS($B$4:$B$101,">="&DATE(2024,5,1),$B$4:$B$101,"<="&DATE(2024,5,2),E4:E100,"W"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4:F57,F62:F105Expression=$P4="Declined"textNO
F4:F57,F62:F105Expression=$P4="Completed - Account"textNO
F4:F57,F62:F105Expression=$P4="Complete"textNO
E4:E62Cell Valuecontains "L"textNO
E4:E62Cell Valuecontains "W"textNO
C4:D57,B4:B105,C59:C105,D62:D105Expression=$R4="Completed - Account"textNO
C4:D57,B4:B105,C59:C105,D62:D105Expression=$R4="Complete"textNO
C4:D57,B4:B105,C58:C105,D62:D105Expression=$R4="Declined"textNO
C4:C105Expression=$R4="TO DO"textNO
Cells with Data Validation
CellAllowCriteria
C2:C3Any value
E4:E11ListW, L
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Does this do what you intend?

Excel Formula: 
=SUMIFS($D$4:$D$101,$B$4:$B$101,H4,$E$4:$E$101,"W")
 
Upvote 0
On reviewing your formula in J4, I can see that your ranges are not all the same size. If you change $E$4:$E$100 to $E$4:$E$101 or $E$3:$E$100, does that give you what you're after?
Book1
BCDEFGHIJ
2DateJob ReferenceInitial Quote TotalW/LFinal Invoice Total# of Leads per dateSales Per Date
32024-05-0100
42024-05-02Wang3964.31W3964.312024-05-0211
52024-05-10Pethig3790.4L2024-05-0300
62024-05-11Wylde5721.31W5304.922024-05-0401
72024-05-13Young1185W11852024-05-0501
82024-05-14O'brien3255.53W3255.532024-05-0601
92024-05-15Parker1273.15W1273.152024-05-0701
102024-05-16Theobald9880W8707.692024-05-0801
112024-05-17Rodwell3975.842024-05-0900
Sheet1
Cell Formulas
RangeFormula
I3:I11I3=COUNTIF(B:B,H3)
J3:J11J3=SUM(COUNTIFS($B$4:$B$101,">="&$H$3,$B$4:$B$101,"<="&H3,E3:E100,"W"))
 
Upvote 0
Asbestos_Jen said:
Does this do what you intend?

Excel Formula: 
=SUMIFS($D$4:$D$101,$B$4:$B$101,H4,$E$4:$E$101,"W")
Click to expand...
Asbestos_Jen said:
Does this do what you intend?

Excel Formula: 
=SUMIFS($D$4:$D$101,$B$4:$B$101,H4,$E$4:$E$101,"W")
Click to expand...
Hi, That solves my next step in the Dashboard im making, but I need the total number of won (W) orders for the specified date. apologies for the confusion
 
Upvote 0
Asbestos_Jen said:
On reviewing your formula in J4, I can see that your ranges are not all the same size. If you change $E$4:$E$100 to $E$4:$E$101 or $E$3:$E$100, does that give you what you're after?
Book1
BCDEFGHIJ
2DateJob ReferenceInitial Quote TotalW/LFinal Invoice Total# of Leads per dateSales Per Date
32024-05-0100
42024-05-02Wang3964.31W3964.312024-05-0211
52024-05-10Pethig3790.4L2024-05-0300
62024-05-11Wylde5721.31W5304.922024-05-0401
72024-05-13Young1185W11852024-05-0501
82024-05-14O'brien3255.53W3255.532024-05-0601
92024-05-15Parker1273.15W1273.152024-05-0701
102024-05-16Theobald9880W8707.692024-05-0801
112024-05-17Rodwell3975.842024-05-0900
Sheet1
Cell Formulas
RangeFormula
I3:I11I3=COUNTIF(B:B,H3)
J3:J11J3=SUM(COUNTIFS($B$4:$B$101,">="&$H$3,$B$4:$B$101,"<="&H3,E3:E100,"W"))
Click to expand...
Hi, This is really close - is there a way we can make it select only 1 date to tally up per cell?
 
Upvote 0
Do you want it to add only values where the dates in B = dates in H?
Book1
BCDEFGHIJ
2DateJob ReferenceInitial Quote TotalW/LFinal Invoice Total# of Leads per dateSales Per Date
32024-05-0100
42024-05-02Wang3964.31W3964.312024-05-0211
52024-05-10Pethig3790.4L2024-05-0300
62024-05-11Wylde5721.31W5304.922024-05-0400
72024-05-13Young1185W11852024-05-0500
82024-05-14O'brien3255.53W3255.532024-05-0600
92024-05-15Parker1273.15W1273.152024-05-0700
102024-05-16Theobald9880W8707.692024-05-0800
112024-05-17Rodwell3975.842024-05-0900
122024-05-1010
132024-05-1111
142024-05-1200
152024-05-1311
162024-05-1411
172024-05-1511
Sheet1
Cell Formulas
RangeFormula
I3:I17I3=COUNTIF(B:B,H3)
J3:J17J3=SUM(COUNTIFS($B$4:$B$101,"="&H3,$E$4:$E$101,"W"))
 
Upvote 0
Asbestos_Jen said:
Do you want it to add only values where the dates in B = dates in H?
Book1
BCDEFGHIJ
2DateJob ReferenceInitial Quote TotalW/LFinal Invoice Total# of Leads per dateSales Per Date
32024-05-0100
42024-05-02Wang3964.31W3964.312024-05-0211
52024-05-10Pethig3790.4L2024-05-0300
62024-05-11Wylde5721.31W5304.922024-05-0400
72024-05-13Young1185W11852024-05-0500
82024-05-14O'brien3255.53W3255.532024-05-0600
92024-05-15Parker1273.15W1273.152024-05-0700
102024-05-16Theobald9880W8707.692024-05-0800
112024-05-17Rodwell3975.842024-05-0900
122024-05-1010
132024-05-1111
142024-05-1200
152024-05-1311
162024-05-1411
172024-05-1511
Sheet1
Cell Formulas
RangeFormula
I3:I17I3=COUNTIF(B:B,H3)
J3:J17J3=SUM(COUNTIFS($B$4:$B$101,"="&H3,$E$4:$E$101,"W"))
Click to expand...
Yeah, So if i have 2 sales confirmed on x date, i want the total (2) to show in J(x) So yes - the dates in B have to match the date in H(x) to = Total
 
Upvote 0
Ok, I expanded your data to have one "L" and two "W" leads on May 17, and that shows up in J19.
Book1
BCDEFGHIJ
2DateJob ReferenceInitial Quote TotalW/LFinal Invoice Total# of Leads per dateSales Per Date
32024-05-0100
42024-05-02Wang3964.31W3964.312024-05-0211
52024-05-10Pethig3790.4L2024-05-0300
62024-05-11Wylde5721.31W5304.922024-05-0400
72024-05-13Young1185W11852024-05-0500
82024-05-14O'brien3255.53W3255.532024-05-0600
92024-05-15Parker1273.15W1273.152024-05-0700
102024-05-16Theobald9880W8707.692024-05-0800
112024-05-17Rodwell3975.84W3975.842024-05-0900
122024-05-17Jones1234.56W1234.562024-05-1010
132024-05-17Smith4567.54L2024-05-1111
142024-05-1200
152024-05-1311
162024-05-1411
172024-05-1511
182024-05-1611
192024-05-1732
Sheet1
Cell Formulas
RangeFormula
I3:I19I3=COUNTIF(B:B,H3)
J3:J19J3=SUM(COUNTIFS($B$4:$B$101,"="&H3,$E$4:$E$101,"W"))
 
Upvote 0
The "SUM" part of the equation in J3 isn't actually doing anything, so the following is better:
Excel Formula: 
=COUNTIFS($B$4:$B$101,"="&H3,$E$4:$E$101,"W")
 
Upvote 0
Holy heck you did it!! Thank you so much. I've been trying to get this to work for three days haha.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
To Find the difference of values through their crossing points on the Graph
Replies
2
Views
515
Fluff
Fluff
P
  • Question Question
Help! Trouble making a interactive graph and embedding data
Replies
0
Views
3K
pulary
P
A
  • Question Question
#Value returned in cell, when Formula window return correct
Replies
3
Views
2K
ariel998
A

Forum statistics

Threads
1,223,880
Messages
6,175,157
Members
452,615
Latest member
bogeys2birdies

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