YouTubersLoveExcel#45: SUMPRODUCT & TEXT functions

I heard that sumproduct is very useful. Can I know the functions of it.

Thank you

Hi all,

Can someone help with this formula,

Cell $A$24 = A cell formatted as Month and Year = July06
Cell $B$1 = a date 1/7/06 linked to $A$24

Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!$D$2:$D$247

This is what I've got

=SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247>= "&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$247="&$A2)))))

Any help would be appreciated


VBA Noob

My problem is two fold, first I am trying to count the number of records that fit multiple criteria from multiple columns and second, trying to convert those formula's into VBA.

Basically I need a VBA solution that will do this:

1)On sheet1 click on a cell.

2)run the macro,

3) Search sheet1 for the number of rows that have values T, U, V, W, X or Y in column K AND do not contain value W in column L.

4) Return the number of rows matching that criteria in the active cell on sheet 1 (named Lots Insp) as a value, not the formula.

I have come up with the following formula that does what I need, but I think there is probably a simpler more efficient way:
Code:

=COUNTIF('Lots Insp'!K2:K100,"N/A")+COUNTIF('Lots Insp'!K2:K100,"PRODUCTION")+COUNTIF('Lots Insp'!K2:K100,"LIMITED-RELEASE")+COUNTIF('Lots Insp'!K2:K100,"PRE-RELEASE")+COUNTIF('Lots Insp'!K2:K100,"FAI")+COUNTIF('Lots Insp'!K2:K100,"S.O.")-COUNTIF('Lots Insp'!O2:O100,"NCR")

1) Is this an appropriate method or is there a simpler way to accomplish the same thing?

2) How can I convert the formula into VBA so I can run it as a macro from any selected cell on sheet1

Thanks in advance!!!

My problem is two fold, first I am trying to count the number of records that fit multiple criteria from multiple columns and second, trying to convert those formula's into VBA.

Basically I need a VBA solution that will do this:

1)On sheet1 click on a cell.
2)run the macro
3) Search sheet1 for the number of rows that have values T, U, V, W, X or Y in column K AND do not contain value W in column L.
4) Return the number of rows matching that criteria in the active cell on sheet 1 (named Lots Insp) as a value, not the formula.

I have come up with the following formula that does what I need, but I think there is probably a simpler more efficient way:
Code:

=COUNTIF('Lots Insp'!K2:K100,"T")+COUNTIF('Lots Insp'!K2:K100,"U")+COUNTIF('Lots Insp'!K2:K100,"V")+COUNTIF('Lots Insp'!K2:K100,"W")+COUNTIF('Lots Insp'!K2:K100,"X")+COUNTIF('Lots Insp'!K2:K100,"Y")-COUNTIF('Lots Insp'!O2:O100,"Z")

1) Is this an appropriate method or is there a simpler way to accomplish the same thing?

2) How can I convert the formula into VBA so I can run it as a macro from any selected cell on sheet1

Thanks in advance!!!

My spreadsheet has two separate sheets. One is a data list and the other is a report sheet. Here's how my data sheet looks:

Code:

Column A   Column B
   1         Mike
   1         John
   1         Bob
   1         Bill
   3         Jake
   3         Ted
   3         Josh
   3         Kevin
   3         Ray
etc...

As you can see the column A represents a group number and column B represents a persons name that is in that group. In my report sheet I'm trying to create a formula that will count every name for that is in each group. Here's how my report sheet would look:

Code:

Column A Column B
   1        4
   3        5

Column A again represents the group number while column B now just shows that total number in each group. I'm guessing I need to use a combination of a sumproduct and count function but I'm having trouble putting it together. Any help would be greatly appreciated.

Hi,

i'm trying to perform an operation that gets the standard deviation of all open status.

please see my formula.

=SUMPRODUCT(($D$4:$D$1591="open")*1,($H$4:$H$1591)*1,(STDEVA($H$4:$H$1591)))

please assist. thanks a much

Hey all.

As we probably know, this:
=SUMPRODUCT(A1:D1,A2:D2)

is like saying
=A1*A2 + B1*B2+C1*C2+D1*D2

Seems that using an Arrary (or Crtl Shift Enter or CSE) function, the following
should also work (of course you don't include the brackets!):
{=A1:D1*A2:D2}
or
{=(A1:D1)*(A2:D2)}

but I'm not feeling any sunshine here.

Ideas?

Guys,

I have a table where I fill-in various info:



I would like to focus on the last three column headers/ date/month/year (used formula to extract month and the year from the date)

Then on another sheet I have new tables where I sum the information by "persons, companies etc..." only when specific conditions have been met using SUMPRODUCT, here's the other sheet (look at the formula bar to see the actual formula I use):



Now, is there a way not to use =MONTH() and =YEAR() (delete last two columns completely) functions but to somehow combine these with the existing SUMPRODUCT and meet date related conditions only from the date column (save space on the sheet and make it more straight-forward), current formula e.g.:

=SUMPRODUCT(--(Table1[year]=2002),--(Table1[Name]="Test"),--(Table1[month]=1),Table1[iznos izdatih faktura])

instead of using refs to "year" and "month" to find specific results can I combine some other function with sumproduct to e.g. calculate "broj izdatih faktura" for 11/2002 WITHOUT using the separate columns/ last two on the right...???

Guess I was understandable enough

Thx!

Trying to make this work to pull the month then do the sumproduct part but failing. Would appreciate any help:

=MONTH('2008 Data'!$B$3:$B$8477=1),SUMPRODUCT('2008 Data'!$E$3:$E$8477=$G$3)*('2008 Data'!$I$3:$I$8477)/1000000

Thanks

Hi,

Is there a way of making this shorter and maybe a bit faster given that SUMPRODUCT is already a bit slugish.

=ROUND((SUMPRODUCT(--(May!$A$5:$A$1000=$BH20),--(May!$B$5:$B$1000=$BI20),(May!$C$5:$C$1000))+SUMPRODUCT(--(May!$A$5:$A$1000=$BH20),--(May!$B$5:$B$1000=$BJ20),(May!$C$5:$C$1000))+SUMPRODUCT(--(May!$A$5:$A$1000=$BH20),--(May!$B$5:$B$1000=BK20),(May!$C$5:$C$1000))+SUMPRODUCT(--(May!$A$5:$A$1000=$BH20),--(May!$B$5:$B$1000=BL20),(May!$C$5:$C$1000))+SUMPRODUCT(--(May!$A$5:$A$1000=$BH20),--(May!$B$5:$B$1000=$BM20),(May!$C$5:$C$1000)))/1000,0)

If you notice, there are 5 different pairs of conditions that need to be met and to add May!$C$5:$C$1000, but (May!$A$5:$A$1000=$BH20) is in each of the the five pairs of conditions.


Thanks,

Ron

EDIT: Do you think I am over-doing the SUMPRODUCT thingy. I use that formula in apprx 50 cells per column. There are 24 columns on each worksheet, and 14 worksheets in that workbook