Sumproduct OR adding criteria to Countif, Counta and Average

W

Wkruger

New Member
Joined
Feb 19, 2010
Messages
28
I trying to convert the following formula (I, II, III) and add criteria of Name and Date to the count values of “c”

CURRENT FORMULA

I: “=COUNTIF(C2:C3100,"<=6")” versley (B) and today to last 7 days (A)
II: “=COUNTA(C2:C3100)” versley (B) and today to last 7 days (A)
III: “=AVERAGE(C2:C3100)” versley (B) and today to last 7 days (A)

Additional criteria

Date (A ): (A2:A3100<=TODAY())*(CA:A3100>=TODAY()-7))
Name (B): B2:B3100="vensley"
Count (C): Less than conditions

Sorry if I was not clear enough and appreciate any help

Thank you,
Wm…

<TABLE style="WIDTH: 261pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=346 border=0 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><TBODY><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl24 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: #003366" width=102 height=27>Date Received</TD><TD class=xl24 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: #003366" width=138>Team</TD><TD class=xl24 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc 0.5pt solid; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: #003366" width=106>Grade</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40193">15-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>Susan</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>9</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40200">22-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>William</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>10</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40205">27-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>Mike</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>10</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40220">11-Feb-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>Mike</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>7</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40184">6-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>vensley</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>0</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40185">7-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>vensley</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>6</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40193">15-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>vensley</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>3</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40193">15-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>vensley</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>8</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40198">20-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>FGreene</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>9</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40203">25-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>FGreene</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>9</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40205">27-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>FGreene</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>10</TD></TR><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD class=xl25 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: #0066cc 0.5pt solid; HEIGHT: 20.25pt; BACKGROUND-COLOR: white" width=102 height=27 x:num="40205">27-Jan-10</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 104pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=138>FGreene</TD><TD class=xl26 style="BORDER-RIGHT: #0066cc 0.5pt solid; BORDER-TOP: #0066cc; BORDER-LEFT: #0066cc; WIDTH: 80pt; BORDER-BOTTOM: #0066cc 0.5pt solid; BACKGROUND-COLOR: white" width=106 x:num>10</TD></TR></TBODY></TABLE>
 
Sort by date Sort by votes
I reviewed again and I did see a few errors - sorry and ty. I double check and believe I now copied as you suggested. I also moved the data so its on the same page.

<TABLE style="WIDTH: 638pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=850 border=0 x:str><COLGROUP><COL style="WIDTH: 638pt; mso-width-source: userset; mso-width-alt: 31085" width=850><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl24 id=td_post_2259398 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 638pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" width=850 height=17 x:str="'=SUM(IF($B$25:$B$3100=F2,IF($A$25:$A$3100>=F1,IF($A$25:$A$3100<=F1-7,IF($C$25:$C$3100<=F3,1)))))">=SUM(IF($B$25:$B$3100=F2,IF($A$25:$A$3100>=F1,IF($A$25:$A$3100<=F1-7,IF($C$25:$C$3100<=F3,1)))))</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" height=17 x:str="'=SUM(IF($B$25:$B$3100=F2,IF($A$25:$A$3100>=F1,IF($A$25:$A$3100<=F1-7,1))))">=SUM(IF($B$25:$B$3100=F2,IF($A$25:$A$3100>=F1,IF($A$25:$A$3100<=F1-7,1))))</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" height=17 x:str="'=AVERAGE(IF($B$25:$B$3100=F2,IF($A$2:$A$3100>=F1,IF($A$25:$A$3100<=F1-7,$C$25:$C$3100))))">=AVERAGE(IF($B$25:$B$3100=F2,IF($A$2:$A$3100>=F1,IF($A$25:$A$3100<=F1-7,$C$25:$C$3100))))</TD></TR></TBODY></TABLE>

Current error: #Value!

Wm...
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
I am still not convinced about the greater than and less than signs. To me it looks like they should have been the other way round.

I would have done it like this (Shift+Ctrl+Enter):

1. Number of occurences where the date in column A is in the last 7 days, Name in column B is "versley", and the value in column C is less than or equal to 6.
=COUNT(IF($A$2:$A$3100<=TODAY(),IF($A$2:$A$3100>(TODAY()-7),IF($B$2:$B$3100="versley",IF($C$2:$C$3100<=6,$C$2:$C$3100)))))


2. Number of occurences where the date in column A is in the last 7 days, Name in column B is "versley".
=COUNT(IF($A$2:$A$3100<=TODAY(),IF($A$2:$A$3100>(TODAY()-7),IF($B$2:$B$3100="versley",$C$2:$C$3100))))


3. Average of the values in column C where the date in column A is in the last 7 days, Name in column B is "versley".
=AVERAGE(IF($A$2:$A$3100<=TODAY(),IF($A$2:$A$3100>(TODAY()-7),IF($B$2:$B$3100="versley",$C$2:$C$3100))))
 
Upvote 0
sachin.acharya said:
I am still not convinced about the greater than and less than signs. To me it looks like they should have been the other way round.

I would have done it like this (Shift+Ctrl+Enter):

1. Number of occurences where the date in column A is in the last 7 days, Name in column B is "versley", and the value in column C is less than or equal to 6.
=COUNT(IF($A$2:$A$3100<=TODAY(),IF($A$2:$A$3100>(TODAY()-7),IF($B$2:$B$3100="versley",IF($C$2:$C$3100<=6,$C$2:$C$3100)))))


2. Number of occurences where the date in column A is in the last 7 days, Name in column B is "versley".
=COUNT(IF($A$2:$A$3100<=TODAY(),IF($A$2:$A$3100>(TODAY()-7),IF($B$2:$B$3100="versley",$C$2:$C$3100))))


3. Average of the values in column C where the date in column A is in the last 7 days, Name in column B is "versley".
=AVERAGE(IF($A$2:$A$3100<=TODAY(),IF($A$2:$A$3100>(TODAY()-7),IF($B$2:$B$3100="versley",$C$2:$C$3100))))
Click to expand...

If they must be other way around, just change them that way. There is no need to replace SUM with COUNT, which causes slower performance.
 
Upvote 0
Aladin,

Thank you for your insight.

If I wanted to count just today I would remove "IF($A$2:$A$3100>(TODAY()-7)" and keep the rest the same for each formula? Or if i wanted to look at all names I would remove "IF($B$2:$B$3100="versley"? and keep all other the same, again for each formula?

By the way, I think one reason I had problems is the Shift+Ctrl+Enter. I must not hit the sequence correctly or some things. Anyway thanks again

Have a great day!

Wm...
 
Upvote 0
Thanks Aladin. I didn't know that SUM is faster than COUNT. That's my learning for the day.

Wm, for your understanding, here are the steps to write your own array formula:

1. Start with the output you want, like SUM, COUNT, AVERAGE, etc.
2. Open the parentheses ( and keep adding your conditions as
IF(Condition1,IF(Condition2,IF(Condition3,IF(Condition4, and so on..
3. Close all the IF parentheses
4. Enter the range you want the output from
5. Close the parentheses for your SUM, COUNT, or AVERAGE etc.
6. Press Shift+Ctrl+Enter

Hope this helps.
 
Upvote 0
Sachin,

Thank you for the rules. As you can tell Excel is not something I have used in the past much, but in the last 2 weeks I see how powerful it is.

I do appreciate your patience, clarity and details.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Cheers,
William
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
865
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
VBA for Web Scraping help
Replies
0
Views
624
epactheactor
E
R
  • Question
Random
Replies
1
Views
460
Fluff
Fluff

Forum statistics

Threads
1,216,107
Messages
6,128,869
Members
449,475
Latest member
Parik11

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