Counting values from other sheets with variables

fapb48

New Member
Hi guys,

I have two sheets. One is the master sheet with all the data. And the other is a sheet that i am pulling the totals from each specific value from.

On the master sheet i have a cell that acts like a week number.

I have many rows each one stating a week number and then i have enter a specific value on another column.
The total row calculates all values entered on that column but it changes according to the week number.

All i want to do is pull that data to another sheet. but instead of calculating all values for that specific week i want to calculate the total of each different value for that week.

I have attached a screenshot of my master sheet to see if it makes more sense.

On the green cell if i change to 13 it will count all values that were added on week 13.

I want to the same on the other sheet. As soon as i change the week number it pulls only the info for that week. But then again i want only the values for that specific week only

This is what i have tried but without success

B1 on my sheet is where i type the week number

=IF(\$B\$1='Values'!\$G\$2,COUNTIF('Values'!J4:J,"SV9))

Because otherwise it will count all values with SV9 on that column and i have over a 1000 rows. and i dont to count all of those rows

Im sorry if i cant explain any better but hope this makes sense

Attachments

• Untitled1.png
31.5 KB · Views: 4

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

etaf

Well-known Member
=countifs(Range with week number in values sheet, B1 , Range with SV9 in values sheet, "SV9" )

fapb48

New Member
Beautiful. thank you very much.

Last question is.. How do i make the formula to count in a specific manner.

For example SV9F needs to count as "1" but SV9H needs to count as"0.5"

fapb48

New Member
I have tried this but it gives me an error return

=COUNTIFS(('DOX2'!G4:\$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 F")+0.5*COUNTIFS('DOX2'!G4:\$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 H"))

etaf

Well-known Member

you have a 2nd thread running
which seems to be the same question

Anyway - seems you have closed that one down
DOX2'!G4:\$G
should be a range
DOX2'!G4:\$G100
or
just the column
DOX2'!\$G:\$G

why does it not work ?

Last edited:

etaf

Well-known Member
change the brackets
=COUNTIFS('DOX2'!G4:\$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 F")+(0.5*COUNTIFS('DOX2'!G4:\$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 H"))
make sure
=COUNTIFS('DOX2'!G4:\$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 F") works with no error
and same for
=(0.5*COUNTIFS('DOX2'!G4:\$G, B1,'DOX2'!J4:J,"NL-STD-SV-9 H"))
BUT needs a range

fapb48

New Member
I have got it. Thank you very much for your help

you are welcome

Replies
0
Views
79
Replies
9
Views
202
Replies
0
Views
38
Replies
0
Views
88
Replies
6
Views
65