Counting values from other sheets with variables

fapb48

New Member
Joined
Sep 13, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
    Untitled1.png
    31.5 KB · Views: 4

Some videos you may like

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
Joined
Oct 24, 2012
Messages
4,164
Office Version
  1. 365
Platform
  1. MacOS
=countifs(Range with week number in values sheet, B1 , Range with SV9 in values sheet, "SV9" )
 

fapb48

New Member
Joined
Sep 13, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 13, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
4,164
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Oct 24, 2012
Messages
4,164
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Sep 13, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I have got it. Thank you very much for your help
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,164
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,119,061
Messages
5,575,872
Members
412,689
Latest member
nhsmedic
Top