| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help writing function
Column A has Days of the week listed for the entire month
Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help writing function
Hi,
Let's assume that you have a list of you days of the week in column C starting in row 2, in column D enter =SUMPRODUCT(($A$2:$A$100=C2)*($B$2:$B$100)) Copy formula down, change range to fit your needs "LQQKB4uleep" wrote: Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help writing function
If weekdays are as text then
=SUMIF(A:A,"Sunday",B:B) 'If colA contains dates formatted as weekdays then try =SUMPRODUCT((WEEKDAY(A2:A100)=1)*(B2:B100)) If this post helps click Yes --------------- Jacob Skaria "LQQKB4uleep" wrote: Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help writing function
In c1 put:
=SUMIF(A:A,A1,B:B) Change the "A1" part to match the day you want to total. Squeaky "LQQKB4uleep" wrote: Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help writing function
None of these suggestions worked, any other ideas please...anyone?
"LQQKB4uleep" wrote: Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help writing function
Hi,
which result do you get, the name in column c for my formula has to be exactly the same as column A, if you typed the name in column C check for blank spaces in column A. or take the names from column A and copy into C "LQQKB4uleep" wrote: None of these suggestions worked, any other ideas please...anyone? "LQQKB4uleep" wrote: Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help writing function
Hi,
be careful the range has to be the same in both part of the formulas I know the formula works I checked I you don't find the error please post the formula you are using again , thanks "Eduardo" wrote: Hi, which result do you get, the name in column c for my formula has to be exactly the same as column A, if you typed the name in column C check for blank spaces in column A. or take the names from column A and copy into C "LQQKB4uleep" wrote: None of these suggestions worked, any other ideas please...anyone? "LQQKB4uleep" wrote: Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help writing function
To count try
'Weeks as text =SUMPRODUCT((A2:A100="Sunday")*(B2:B100<"")) 'Col A contains dates formatted as weekdays =SUMPRODUCT((WEEKDAY(A2:A100)=1)*(B2:B100<"")) If this post helps click Yes --------------- Jacob Skaria "LQQKB4uleep" wrote: None of these suggestions worked, any other ideas please...anyone? "LQQKB4uleep" wrote: Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help writing function
LQQKB4uleep,
Each of these should have worked for you given the information you provided. In your example, what cell is the first "Sunday" in? In my formula I assumed A1, and the formula is placed in C1. If the first Sunday is not in A1, whatever cell it is in exchange that for "A1", then drag it down for the other days of the week. My Formula: =SUMIF(A:A,A1,B:B) =SUMIF(A:A,A2,B:B) =SUMIF(A:A,A3,B:B) Jabob's first formula basically said the same thing as mine except he put "Sunday" in place of A1. With his, you would need to drag it down and then replace the "Sunday" with another day. Jacob's formula: =SUMIF(A:A,"Sunday",B:B) =SUMIF(A:A,"Monday",B:B) =SUMIF(A:A,"Tuesday",B:B) "LQQKB4uleep" wrote: Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
need help writing function
Hi,
you could create a pivot table. Drag Days to the row area and numbers to the data area. Right click any number in the data area and select value field settigns. Change the "Summarise by" field from sum to count. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "LQQKB4uleep" wrote in message ... Column A has Days of the week listed for the entire month Column B has data listed for days of the week Want formula to count data in column B if it equals a certain day of the week like if sunday then count all data listed for sundays in column B "a" "B" Sunday 21 Monday 15 Tuesday 28 Sunday 14 Monday 10 Tuesday 12 sunday=35 Monday=25 Tuesday=40 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need Help With Writing Concatenate Function | Excel Worksheet Functions | |||
| Function writing help | Excel Worksheet Functions | |||
| WRITING A WHAT IF FUNCTION | Excel Worksheet Functions | |||
| Why doesn't writing a nested IF function work? | Excel Discussion (Misc queries) | |||
| Help Writing Function | Excel Worksheet Functions | |||
Linear Mode
