need help writing function

L

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
 
E

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
 
J

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
 
S

Squeaky

In c1 put:

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

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

Squeaky
 
E

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
 
E

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
 
J

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
 
S

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)
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top