I am doing a count with multiple criteria so i am using a sum function to do
sum(criteria1)*criteria 2
However, the second criteria is if there is a value in the cell. How do i do this?
I am doing a count with multiple criteria so i am using a sum function to do
sum(criteria1)*criteria 2
However, the second criteria is if there is a value in the cell. How do i do this?
You mean?
=Sumproduct(--(range1=criteria1),--Isnumber(range2))
or
=Sumproduct(--(range1=criteria1),--(range2<>""))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
If you're using SUMPRODUCT, perhaps you're looking for something like:
=SUMPRODUCT((range1="x")*(range2<>""))
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
thank you! the isnumber works. what about if the cell is empty, how do i count that
also, i need to figure out a function so if a name is in an array i need to add the values from the ajacent cells. the name can reoccur within the first column, so everytime it appears needs to add in the next column. ideas?
You can use SUMIF, e.g.
=SUMIF(A:A,"Dave",B:B)
that will sum all rows in column B where column a = "Dave". If you have your name in C2 you can make that
=SUMIF(A:A,C2,B:B)
Audere est facere
thank you! what about counting empty cells?
=Sumproduct(--(range1=criteria1),--(range2=""))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks