need help writing function

[LQQKB4uleep]

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

 

[Eduardo]

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

 

[Jacob Skaria]

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

 

[Squeaky]

In c1 put:

=SUMIF(A:A,A1,B:B)

Change the "A1" part to match the day you want to total.

Squeaky

 

[LQQKB4uleep]

None of these suggestions worked, any other ideas please...anyone?

 

[Eduardo]

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

 

[Eduardo]

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

 

[Jacob Skaria]

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

 

[Squeaky]

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)

 

[Ashish Mathur]

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